I did not want to have to do two transactions in the database, currently I'm doing SELECT first to know if the record already exists in the table, then I do or INSERT or UPDATE depending on the case, to do though a transaction is possible? something like INSERT .... IF NOT EXISTS THEN UPDATE ... ?
MYSQL - Check if PK exists if yes does UPDATE if it does not exist INSERT
4
asked by anonymous 12.09.2018 / 20:50
2 answers
7
You can use ON DUPLICATE KEY :
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
name="A", age=19
See working in SQLFiddle , with ON DUPLICATE KEY and REPLACE .
References
12.09.2018 / 20:55
5
It may be interesting to 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.
Example usage:
REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Partitioning and locking
REPLACE INTO only blocks partitions with rows to be inserted or replaced. However, if an AUTO_INCREMENT value is generated for any partitioning column, all partitions will be locked.
12.09.2018 / 21:15