Jared Eitnier Jared Eitnier - 1 year ago 58
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.


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

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

I've tried something like this:

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

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

Answer Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download