# [Know Issue]关于 TiDB 在一条 update 语句同时更新多个字段的场景中与 MySQL 语义不同

## Key Words

兼容性,更新多字段,级联更新

## Description

如下述例子，想同时修改 c 字段及 pad 字段，pad 字段无法被修改成预期的值。

```sql
mysql> select * from sbtest2 where id = 3217;
+------+------+-----+-----+
| id   | k    | c   | pad |
+------+------+-----+-----+
| 3217 | 1100 | 123 | 123 |
+------+------+-----+-----+
1 row in set (0.03 sec)

mysql> update sbtest2 set c='123-time2',pad=c where id = 3217;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from sbtest2 where id = 3217;
+------+------+-----------+-----+
| id   | k    | c         | pad |
+------+------+-----------+-----+
| 3217 | 1100 | 123-time2 | 123 |
+------+------+-----------+-----+
1 row in set (0.01 sec) 
```

## Root Cause

这是因为 “TiDB 遵守标准的 SQL 行为” ，[详情参考官网 : MySQL 兼容性](https://docs.pingcap.com/zh/tidb/stable/sql-statement-update#mysql-%E5%85%BC%E5%AE%B9%E6%80%A7) 部分。

## Resolution

存在一种绕过方式，把先算出来的值与原表的原字段 join 上，并用 join 的值去给想要的 update 字段赋值，例子如下。

```sql
mysql> update sbtest2 as t join (select c, '这是一个新值' as new_c from sbtest2 where id=3217) as tmp on t.c=tmp.c set t.c=tmp.new_c, t.pad=new_c where id=3217;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> SELECT * FROM sbtest2 WHERE id = 3217;
+------+------+--------------------+--------------------+
| id   | k    | c                  | pad                |
+------+------+--------------------+--------------------+
| 3217 | 1100 | 这是一个新值       | 这是一个新值       |
+------+------+--------------------+--------------------+
1 row in set (0.01 sec)

mysql> update sbtest2 as t join (select c, '这是二个新值' as new_c from sbtest2 where id=3217) as tmp on t.c=tmp.c set t.c=tmp.new_c, t.pad=new_c where id=3217;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM sbtest2 WHERE id = 3217;
+------+------+--------------------+--------------------+
| id   | k    | c                  | pad                |
+------+------+--------------------+--------------------+
| 3217 | 1100 | 这是二个新值       | 这是二个新值       |
+------+------+--------------------+--------------------+
1 row in set (0.00 sec)

```

## Affected Versions

ALL