UPDATE

Description

The UPDATE statement is used to modify the existing records in a table.

Syntax

Single-table Syntax

  1. UPDATE table_reference
  2. SET assignment_list
  3. [WHERE where_condition]
  4. [ORDER BY ...]
  5. [LIMIT row_count]

Explanations

  • The UPDATE statement updates columns of existing rows in the named table with new values.
  • The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.
  • The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated.
  • If the ORDER BY clause is specified, the rows are updated in the order that is specified.
  • The LIMIT clause places a limit on the number of rows that can be updated.

Examples

  • Single-table Examples
  1. CREATE TABLE t1 (a bigint(3), b bigint(5) primary key);
  2. insert INTO t1 VALUES (1,1),(1,2);
  3. update t1 set a=2 where a=1 limit 1;
  4. mysql> select * from t1;
  5. +------+------+
  6. | a | b |
  7. +------+------+
  8. | 2 | 1 |
  9. | 1 | 2 |
  10. +------+------+
    • Multiple-table Examples
  1. drop table if exists t1;
  2. create table t1 (a int);
  3. insert into t1 values(1), (2), (4);
  4. drop table if exists t2;
  5. create table t2 (b int);
  6. insert into t2 values(1), (2), (3);
  7. update t1, t2 set a = 1, b =2;
  8. mysql> select * from t1;
  9. +------+
  10. | a |
  11. +------+
  12. | 1 |
  13. | 1 |
  14. | 1 |
  15. +------+
  16. update t1, t2 set a = null, b =null;
  17. mysql> select * from t2;
  18. +------+
  19. | b |
  20. +------+
  21. | NULL |
  22. | NULL |
  23. | NULL |
  24. +------+
  25. mysql> select * from t1;
  26. +------+
  27. | a |
  28. +------+
  29. | NULL |
  30. | NULL |
  31. | NULL |
  32. +------+

Multiple-table join Syntax is also supported.

  1. drop table if exists t1;
  2. drop table if exists t2;
  3. create table t1 (a int, b int, c int);
  4. insert into t1 values(1, 2, 3), (4, 5, 6), (7, 8, 9);
  5. create table t2 (a int, b int, c int);
  6. insert into t2 values(1, 2, 3), (4, 5, 6), (7, 8, 9);
  7. update t1 join t2 on t1.a = t2.a set t1.b = 222, t1.c = 333, t2.b = 222, t2.c = 333;
  8. mysql> select * from t1;
  9. +------+------+------+
  10. | a | b | c |
  11. +------+------+------+
  12. | 1 | 222 | 333 |
  13. | 4 | 222 | 333 |
  14. | 7 | 222 | 333 |
  15. +------+------+------+
  16. mysql> with t11 as (select * from (select * from t1) as t22) update t11 join t2 on t11.a = t2.a set t2.b = 666;
  17. mysql> select * from t2;
  18. +------+------+------+
  19. | a | b | c |
  20. +------+------+------+
  21. | 1 | 666 | 333 |
  22. | 4 | 666 | 333 |
  23. | 7 | 666 | 333 |
  24. +------+------+------+
  25. 3 rows in set (0.00 sec)