MySQL upate if和case when语法

DROP TABLE t;
CREATE TABLE t(a INT,b INT);
INSERT INTO t SELECT 1,1;
INSERT INTO t SELECT 2,2;
INSERT INTO t SELECT 3,3;
INSERT INTO t SELECT 4,4;
SELECT * FROM t;

mysql> SELECT * FROM t;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
+------+------+

4 rows in set (0.00 sec)

update中的a=IF(a=1,11,a)意思是：当发现a=1的数据时，这条数据的a字段值改为11；否则不改动。

mysql> UPDATE t SET a=IF(a=1,11,a),b=IF(a=2,22,b);
Query OK, 2 rows affected (0.04 sec)
Rows matched: 4  Changed: 2  Warnings: 0


mysql> SELECT * FROM t;
+------+------+
| a    | b    |
+------+------+
|   11 |    1 |
|    2 |   22 |
|    3 |    3 |
|    4 |    4 |
+------+------+
4 rows in set (0.00 sec)

mysql> UPDATE t SET a=IF(a=1,11,111),b=IF(a=2,22,222);
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM t;
+------+------+
| a    | b    |
+------+------+
|  111 |  222 |
|  111 |  222 |
|  111 |  222 |
|  111 |  222 |
+------+------+

4 rows in set (0.00 sec)

还有一种语法

TRUNCATE TABLE t;
INSERT INTO t SELECT 1,1;
INSERT INTO t SELECT 2,2;
INSERT INTO t SELECT 3,3;
INSERT INTO t SELECT 4,4;
mysql> UPDATE t SET
    ->    a = CASE WHEN a=1 THEN 11 ELSE a END,
    ->    b = CASE WHEN a=2 THEN 22 ELSE b END;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 4  Changed: 2  Warnings: 0


mysql> select * from t;
+------+------+
| a    | b    |
+------+------+
|   11 |    1 |
|    2 |   22 |
|    3 |    3 |
|    4 |    4 |
+------+------+
4 rows in set (0.00 sec)