用户登录
用户注册

mysql实现merge into语法的(代码实例)

mysql实现merge into语法的(代码实例)

mysql并不没有oracle、mssql的merge into语法,但是有个on duplicate key update语法(不是标准的sql语法)可以实现merge into语法
实验一:更新所有字段
mysql> select * from dup;
+------+--------+-------+
| id   | name   | phone |
+------+--------+-------+
|    1 | wujian |   123 |
|    2 | xiay   |  1234 |
|    3 | wangj  | 12345 |
+------+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from dupnew;
+------+------+-------+
| id   | name | phone |
+------+------+-------+
|    1 | xyr  |   128 |
|    2 | sy   |     0 |
|    5 | wsj  |  8684 |
+------+------+-------+
3 rows in set (0.00 sec)

mysql>  insert into dup(id,name,phone ) select * from dupnew on duplicate key update name=values(name);
query ok, 3 rows affected (0.06 sec)
records: 3  duplicates: 0  warnings: 0

mysql> select * from dup;
+----+-------+-------+
| id | name  | phone |
+----+-------+-------+
|  1 | xyr   |   123 |
|  2 | sy    |  1234 |
|  3 | wangj | 12345 |
|  5 | wsj   |  8684 |
+----+-------+-------+
4 rows in set (0.00 sec)
结果:实现了将表dupnew更新到表dup中去,存在就更新,不存在就插入
注意:id字段是主键或unique索引,不然只会插入dupnew表所有行数据

实验二:更新部分字段
mysql> select * from dupagn;
+------+------+-------+
| id   | name | phone |
+------+------+-------+
|    1 | myy  |  1888 |
|   10 | wz   |   556 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> insert into dup(id,name) select id,name from dupagn on duplicate key update name=values(name);
query ok, 3 rows affected (0.06 sec)
records: 2  duplicates: 1  warnings: 0

mysql> select * from dup;
+----+-------+-------+
| id | name  | phone |
+----+-------+-------+
|  1 | myy   |   123 |
|  2 | sy    |  1234 |
|  3 | wangj | 12345 |
|  5 | wsj   |  8684 |
| 10 | wz    |  null |
+----+-------+-------+
5 rows in set (0.00 sec)
结果:实现了只更新name字段,但是插入的记录中其他字段就为空了
热门评论
暂无评论,快去抢占沙发吧~

分享至

长按复制下方链接,去粘贴给好友吧:
请使用当前手机浏览器底部中间的“添加书签”功能进行收藏。
我知道了
软件
前端设计
程序设计
Java相关