mysql update语句
2024-10-19 06:28:29
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 |
最新文章
- FacadePattern(门面模式)
- BestCoder Round #86
- Android数据存储方式
- Windows Azure Cloud Service (44) 将Cloud Service加入Virtual Network Subnet，并固定Virtual IP Address(VIP)
- IE6完美解决fix问题
- java UDP 简单实现编程
- 1分钟内检查Linux服务器性能的命令
- Oracle表结构转换SqlSERVER表结构 脚本
- 树莓派连接GPS模块
- scp 对拷文件夹 和 文件夹下的所有文件 对拷文件并重命名
- GridView绑定DataKeyNames以及如何取这些值
- 火车站点城市查询（appserv 服务器练习）
- 51 nod 1628 非波那契树
- 有效的括号(Java实现)
- linux(fedora) 第一课
- Python内置类型(5)--迭代器类型
- CCF CSP 201703-3 Markdown
- kafka 生产消费原理详解
- WPF圆角按钮与触发颜色变化
- 第四章 栈与队列（c4）栈应用：中缀表达式求值