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.
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
REPLACEonly 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
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.”
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.