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 |
+------+------+
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 |
+------+------+
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)