How to update or insert MySQL in the same query?

Question

How to update or insert MySQL in the same query?

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

In PostgreSQL, I have to use upsert . It checks if the line already exists, if it does, it does update, otherwise it does insert.

In MySQL I am not able to do this, I saw some talking to use INSERT ... ON DUPLICATE KEY UPDATE , however all the examples I saw were reporting id.

But I do not have the ID, the ID of my table is automatically generated for every insert I make, so I do not give this ID when making an insert.

mysql sql query

asked by anonymous 23.05.2017 / 05:42

2 answers

Java + Firebird connection returning Null By icon in JFrame

score 2 · Answer 1

The REPLACE statement works as follows:

If the new line does not already exist, insert a new line.
If the new row already exists, the REPLACE statement deletes the old row first, and then insert a new line. In some cases, the statement REPLACE only updates the existing line.

To determine whether the new row already exists in the table, MySQL uses PRIMARY KEY or UNIQUE KEY . If the table does not have one of these indexes, the REPLACE statement is equivalent to the INSERT statement.

Example:

REPLACE INTO cities(id,population) VALUES(2,300000);

If the line with id 2 already exists, it will perform an "update" in the population column. If there is still no line with id 2, it will do an "insert."

score 1 · Answer 2

You can use INSERT with ON DUPLICATE KEY UPDATE.

Just create a single INDEX in your table with the fields you do not want it to duplicate. When you enter a duplicate data, MySQL will execute the UPDATE. Remember that if you try to insert a duplicate data and do not put the ON DUPLICATE KEY UPDATE MySQL will return an error.