MySQL upate if和case when语法

迈不过友情╰ 2022-06-17 14:24 228阅读 0赞

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)