MYSQL ON DUPLICATE KEY UPDATE and IGNORE in one query
Ik runs a query that inserts some values into the table if there is a combination of ID, Year, rownum (unique index), which I do to regularly update the DUPLICATE KEY UPDATE and so the row is updated. The request looks like this:
INSERT INTO data_advertenties_prijzen (`ID`, `year`, `rownum`, `status_prijs`,
`datum_dag`, `timestamp_dag`)
VALUES (100,2014,1,1,'2014-01-01',1388534400),
(100,2014,2,1,'2014-07-16',1405468800),
(100,2014,3,1,'2014-07-26',1406332800)
ON DUPLICATE KEY UPDATE
status_prijs = VALUES(status_prijs),
datum_dag = VALUES(datum_dag),
timestamp_dag = VALUES(timestamp_dag)
Nothing complicated, but ....
I also want to do ON DUPLICATE IGNORE for 1 value in the same query. I also want to insert one line for 2015. For example: (100,2015,1,1, '2015-01-01', 1405468800) ... If there is already a row with ID = 100, Year = 2015 and rownum = 1, the insertion of that row should be ignored.
How to do it?
source to share
You can conditionally change the values in the ON DUPLICATE clause.
I did this experiment to make sure it works:
INSERT INTO data_advertenties_prijzen VALUES (100, 2014, 1, 7, now(), 1406332800)
ON DUPLICATE KEY UPDATE
status_prijs = IF((id,year,rownum)=(100,2015,1), status_prijs, VALUES(status_prijs)),
datum_dag = IF((id,year,rownum)=(100,2015,1), datum_dag, VALUES(datum_dag)),
timestamp_dag = IF((id,year,rownum)=(100,2015,1), timestamp_dag, VALUES(timestamp_dag));
So if I try to insert a specific trio of id / year / rownum, it just uses the existing value, otherwise if it is a different id / year / rownum, it uses the VALUES values I put in the INSERT.
Unfortunately, you have to repeat the expression for every column you want to update.
source to share