mysql update语句
最新推荐文章于 2024-09-23 19:40:25 发布
scan724 于 2016-09-05 14:05:01 发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
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 |
相关知识
mysql数据库知识点整理1，2（自用笔记）
高并发update的 死锁产生原因
MySQL学习笔记
mysql prestatement
宠物店管理系统mysql
Mysql第8章DAO模式上机练习
mysql学习笔记(4)
Java实现Mysql的jdbc连接例子
谷歌云搭建wordpress centos7 + apache + mysql + php + wordpress
mysql 寄件1/1
原文链接: mysql update语句 https://www.mcbbbk.com/newsview633327.html
| 上一篇: 为博妻子开心 买头骆驼当宠物 | 下一篇: 【有图】我爱动物 |
