Jared Eitnier Jared Eitnier - 3 months ago 11
MySQL Question

Mysql update field in table based on another field's condition in same table

I have a table with key/value columns. I need to update a key/value pair based on another value's condition.

Table:

--------------------------
| id | key | value |
--------------------------
| 1 | country | canada |
| 2 | privacy | default |
--------------------------


In this case I need to change
value
to
canada
where
key = privacy
only IF
country = canada
.

I've tried something like this:

UPDATE settings
SET value =
CASE
WHEN key = 'country' AND value = 'canada' THEN 'canada'
ELSE value
END
WHERE key = 'privacy'


but it results in an error on the
CASE
condition. I think I might need to do some sort of sub-query?

Answer

Your statement won't work as you can't have a record with both a key of 'privacy' and 'country'. You need to perform a SELECT on the table itself to check if 'country' is set to 'canada'. Something like the following may work.

UPDATE settings
SET value = 'canada'
WHERE key = 'privacy'
  AND EXISTS(SELECT * FROM (SELECT * FROM settings WHERE key = 'country' AND value = 'canada') AS temp_settings);
Comments