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欄位,但是插入的記錄中其他欄位就為空了

mysql實現merge into語法

相關文章