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

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！