UPDATE ClientBankInfo
SET
status = 3
WHERE
sn IN (SELECT
sn
FROM
zjzc.ClientBankInfo
WHERE
cardNo IN (SELECT
cardNo
FROM
ClientWenJinCardInfo
WHERE
status = 3));
UPDATE ClientBankInfo p, (SELECT
sn
FROM
zjzc.ClientBankInfo
WHERE
cardNo IN (SELECT
cardNo
FROM
ClientWenJinCardInfo
WHERE
status = 3)) pp
SET p.status = 3
where p.sn=pp.sn
关联更新测试；
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
| 4 | a4 |
| 5 | a5 |
| 6 | a6 |
+------+------+
6 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 6 | a6 |
| 5 | a5 |
+------+------+
2 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1,t2 set t1.name='xx' where t1.id=t2.id;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
| 4 | a4 |
| 5 | xx |
| 6 | xx |
+------+------+
6 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 6 | a6 |
| 5 | a5 |
+------+------+
2 rows in set (0.00 sec)
如果：
mysql> update t1,t2 set name='xx' where t1.id=t2.id;
ERROR 1052 (23000): Column 'name' in field list is ambiguous
提示name列是模糊的
/**************
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 6 | a6 |
| 5 | a5 |
+------+------+
2 rows in set (0.00 sec)
mysql> update t1,t2 set name='xx' where t1.id=t2.id;
ERROR 1052 (23000): Column 'name' in field list is ambiguous
mysql> update t1,t2 set t2.name='xx' where t1.id=t2.id;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 6 | xx |
| 5 | xx |
