UPDATE

Grammar Description

UPDATE is used to modify existing records in the table.

Grammar Structure

Single table syntax structure

UPDATE table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Parameter definition

  • UPDATE Updates the new value to the column of an existing row in the specified table.

  • The SET clause indicates which columns to modify and the values they should be assigned. Each value can be given as an expression, or explicitly set the column to the default value via DEFAULT.

  • WHERE clause, which specifies the conditions used to identify which rows to be updated. If there is no WHERE clause, all lines are updated.

  • ORDER BY clause, which refers to updating lines in the specified order.

  • The LIMIT clause is used to limit the number of rows that can be updated.

Example

-Single Example

CREATE TABLE t1 (a bigint(3), b bigint(5) primary key);
insert INTO t1 VALUES (1,1),(1,2);
update t1 set a=2 where a=1 limit 1;

mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 2 | 1 |
| 1 | 2 |
+------+------+

-Multiple Example

drop table if exists t1;
create table t1 (a int);
insert into t1 values(1), (2), (4);
drop table if exists t2;
create table t2 (b int);
insert into t2 values(1), (2), (3);
update t1, t2 set a = 1, b =2;

mysql> select * from t1;
+------+
| a |
+------+
| 1 |
| 1 |
| 1 |
+------+

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

mysql> select * from t2;
+------+
| b |
+------+
| NULL |
| NULL |
| NULL |
+------+
mysql> select * from t1;
+------+
| a |
+------+
| NULL |
| NULL |
| NULL |
+------+

Supports multi-table JOIN statements.

drop table if exists t1;
drop table if exists t2;
create table t1 (a int, b int, c int);
insert into t1 values(1, 2, 3), (4, 5, 6), (7, 8, 9);
create table t2 (a int, b int, c int);
insert into t2 values(1, 2, 3), (4, 5, 6), (7, 8, 9);
update t1 join t2 on t1.a = t2.a set t1.b = 222, t1.c = 333, t2.b = 222, t2.c = 333;

mysql> select * from t1;
+------+------+
| a | b | c |
+------+------+
| 1 | 222 | 333 |
| 4 | 222 | 333 |
| 7 | 222 | 333 |
+------+------+

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

mysql> select * from t2;
+------+------+
| a | b | c |
+------+------+
| 1 | 666 | 333 |
| 4 | 666 | 333 |
| 7 | 666 | 333 |
+------+------+
3 rows in set (0.00 sec)
Last updated on