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字段,但是插入的记录中其他字段就为空了
如对本文有疑问, 点击进行留言回复!!
项目实战: CMDB自动化资产扫描(搞懂这个项目 算你没白学运维)
MySQL中建表时可空(NULL)和非空(NOT NULL)的用法详解
SSD原理及Pytorch代码解读——网络架构(二):特征提取网络及总体计算过程
网友评论