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;

5. mysql> select * from t1;
6. +------+------+
7. | a    | b    |
8. +------+------+
9. |    2 |    1 |
10. |    1 |    2 |
11. +------+------+
  • 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;

9. mysql> select * from t1;
10. +------+
11. | a    |
12. +------+
13. |    1 |
14. |    1 |
15. |    1 |
16. +------+

18. update t1, t2 set a = null, b =null;

20. mysql> select * from t2;
21. +------+
22. | b    |
23. +------+
24. | NULL |
25. | NULL |
26. | NULL |
27. +------+
28. mysql> select * from t1;
29. +------+
30. | a    |
31. +------+
32. | NULL |
33. | NULL |
34. | NULL |
35. +------+

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;

9. mysql> select * from t1;
10. +------+------+------+
11. | a    | b    | c    |
12. +------+------+------+
13. |    1 |  222 |  333 |
14. |    4 |  222 |  333 |
15. |    7 |  222 |  333 |
16. +------+------+------+

18. mysql> with t11 as (select * from (select * from t1) as t22) update t11 join t2 on t11.a = t2.a set t2.b = 666;

20. mysql> select * from t2;
21. +------+------+------+
22. | a    | b    | c    |
23. +------+------+------+
24. |    1 |  666 |  333 |
25. |    4 |  666 |  333 |
26. |    7 |  666 |  333 |
27. +------+------+------+
28. 3 rows in set (0.00 sec)