# **UPDATE**

## **Grammar Description**

`UPDATE` is used to modify existing records in the table.

## **Grammar Structure**

### **Single table syntax structure**

```sql
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**

```text
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**

```text
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.

```text
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)
```