0

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?

Henka Programmer
  • 727
  • 7
  • 25
Jared Eitnier
  • 7,012
  • 12
  • 68
  • 123

1 Answers1

2

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);
Callum George
  • 66
  • 5
  • except I believe it's better practice to use AND EXISTS(SELECT * FROM settings WHERE key = 'country' AND value = 'canada'); – tomb Aug 03 '16 at 21:55
  • You're right. For some reason I forgot EXISTS existed. – Callum George Aug 03 '16 at 22:01
  • I'm getting `Error : You can't specify target table 'settings' for update in FROM clause`. – Jared Eitnier Aug 03 '16 at 22:18
  • My Mistake. In MySQL you can't update the same table you're performing a select on, unless the table is temporary. I've updated the solution, as discussed [here](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) – Callum George Aug 03 '16 at 22:28