Skip to content

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)