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