user2124871 user2124871 - 4 months ago 10
MySQL Question

ON DUPLICATE KEY update (with multiple where clauses)

Having an issue with this query. (mySQL)

I have a primary key (id) and I want to either insert a new value if the conditions don't exist OR update the existing:

INSERT into records (name, value, p_id, c_id)
VALUES ('Store', 'TX', 1188913, 1133)
ON DUPLICATE KEY UPDATE name = 'TX' WHERE p_id = 6188002 and c_id = 77102


So in this case, I would have a record as such that already exists:

id = 10235192
name = 'Store'
value = 'AL'
p_id = 6188002
c_id = 77102


And I would hope that that record is updated from value = 'AL' to 'TX'

But all I'm seeing is a new record inserted. What am I doing wrong?

Answer

You should have specified a UNIQUE constraint on compound column for INSERT...ON DUPLICATE KEY UPDATE to work.

ALTER TABLE records ADD CONSTRAINT tb_uq UNIQUE (p_id, c_id)

and WHERE clause is not needed.

INSERT into records (name, value, p_id, c_id) 
VALUES ('Store', 'TX', 1188913, 1133) 
ON DUPLICATE KEY UPDATE value = 'TX'