Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql how to achieve merge into grammar, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Mysql does not have the merge into syntax of oracle and mssql, but there is an on duplicate key update syntax (not the standard sql syntax) that implements the merge into syntax.

Lab 1: update all fields

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)

Result: update the table dupnew to the table dup, update if there is, and insert if it doesn't exist.

Note: the id field is a primary key or UNIQUE index, otherwise only all rows of data in the dupnew table will be inserted

Experiment 2: update some fields

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)

Result: only the name field is updated, but the other fields in the inserted record are empty

Thank you for reading this article carefully. I hope the article "how to achieve merge into Grammar in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.