How to update or Insert in Mysql in the same query?

Asked

Viewed 1,100 times

3

In Postgresql you can use the upsert. It checks if the line already exists, if yes it does update, otherwise it does Insert.

In Mysql I’m not able to do this, I saw some talking to use INSERT ... ON DUPLICATE KEY UPDATE, but all the examples I saw were informing id.

However I do not have the ID, the ID of my table is generated automatically to every Insert I do, so I do not inform this ID at the time of making an Insert.

mysql sql query

  • I found a solution in Github, maybe I can help you @Isaias.

  • your table beyond the id column has no other one with unique and unique values?

  • @Leocaracciolo has yes, for example, in a table with user data, a unique value could be the email he uses to log in. If the email exists, update, otherwise update.

  • So what exactly is your difficulty? ride that if Else?

  • your PK is auto_increment?

  • @Leocaracciolo Yes, mount if Else using pure sql only. In Postgresql, for example, I just use the UPSERT function, but in Mysql I don’t know how to do it.

  • @Ricardopontual Yes

  • In that case you won’t be able to use insert on duplicate(https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) and the replace (https://dev.mysql.com/doc/refman/5.7/en/replace.html). You will probably need to make one IF to solve this

  • Vlw, by the way it will be easier to do in the same code. kkk

Show 4 more comments

2 answers

2

The statement REPLACE works as follows:

  • If the new line does not yet exist, insert a new line.
  • If the new line already exists, the REPLACE statement deletes the old line first and then inserts a new line. In some cases, the instruction REPLACE just update the existing line.

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

Example:

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

If the line with id 2 already exists, it will do an "update" in the Population column. If there is not yet a line with id 2, it will do an "Insert".

1


You can use insert with on duplicate key update.

Just create a unique INDEX in your table with the fields you don’t want to duplicate. When inserting duplicate data, Mysql will run UPDATE. Remember that if you try to insert duplicate data and do not put the ON DUPLICATE KEY UPDATE Mysql will return an error.

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.