比如这样
update table1 set count=count+1 where (c1,c2) in (select c11,c22 from table2)
这个语句是不能用的,但是我想实现他的效果,怎么变通啊,table1加索引列就免了.
update table1 set count=count+1 where (c1,c2) in (select c11,c22 from table2)
这个语句是不能用的,但是我想实现他的效果,怎么变通啊,table1加索引列就免了.
+------+------+------+
| c1 | c2 | cnt |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | 4 | 1 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 3 | 1 |
| 2 | 4 | 1 |
+------+------+------+
8 rows in set (0.00 sec)mysql> select * from table2;
+------+------+
| c11 | c22 |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
+------+------+
3 rows in set (0.00 sec)mysql> update table1 set cnt=cnt+1 where (c1,c2) in (select c11,c22 from table2)
;
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from table1;
+------+------+------+
| c1 | c2 | cnt |
+------+------+------+
| 1 | 1 | 2 |
| 1 | 2 | 2 |
| 1 | 3 | 1 |
| 1 | 4 | 1 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 3 | 2 |
| 2 | 4 | 1 |
+------+------+------+
8 rows in set (0.00 sec)mysql>