数据库教程:mysql实现merge into语法的（代码实例）

mysql实现merge into语法的（代码实例）

需要了解更多数据库技术:mysql实现merge into语法的（代码实例），都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!

  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字段,但是插入的记录中其他字段就为空了