5
I didn’t want to have to do two transactions in the database, currently I’ve been doing SELECT first to know if the record already exists in the table, then I do either INSERT or UPDATE depending on the case, would you like to do despite a transaction is possible? something like INSERT .... IF NOT EXISTS THEN UPDATE ... ?
8
You can use the ON DUPLICATE KEY:
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
name="A", age=19
See working on Sqlfiddle, with ON DUPLICATE KEY and REPLACE.
exactly what I was looking for, although I find strange the log show me 2 Row(s) affected when there is already the record, I think Mysql is the one who gets the service p/me ta valendo, thanks!
@Sneepsninja I think this question of the log show "2 Row(s)" would make a great new technical question for the great connoisseurs! rs
5
It may be interesting REPLACE INTO
It works like INSERT only when it finds a PK or Uniquekey, the old line is removed and then the new one is inserted.
https://dev.mysql.com/doc/refman/8.0/en/replace.html
Example of use:
REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
The REPLACE INTO locks only partitions with lines to be inserted or replaced. However, if an AUTO_INCREMENT value is generated for any partitioning column, all partitions will be locked.
https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking.html
Sqlfiddle with the 2 examples.
I found it interesting too
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
Are you using a framework? What language? In the Laravel has a method called Createorupdate, checks if it has the record and if it does not have according to the condition it creates.
– Alvaro Alves
Or you can create a Trigger
– Alvaro Alves