mysql update语句

来源：互联网发布：千千静听 mac 老版本编辑：程序博客网时间：2024/05/29 11:24
UPDATE ClientBankInfo SET     status = 3WHERE    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)) ppSET  p.status = 3where 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: 0mysql>  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 ambiguousmysql> 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: 0mysql>  select * from t2;+------+------+| id   | name |+------+------+|    6 | xx   ||    5 | xx   |
0 0