Updating one column in multiple rows

Question

I'm trying to update values in a table that looks like this:

+----+-------------+
| Id | Value       |
+----+-------------+
| 1  | title       |
+----+-------------+
| 2  | description |
+----+-------------+
| 3  | email       |
+----+-------------+
| 4  | keywords    |
+----+-------------+

I would like to update the value if the PK already exists, otherwise insert a new row.

submit.php:

include('../../include/config.php');

if($_POST['settings']){
    $title=$_POST['title'];
    $description=$_POST['description'];
    $email=$_POST['email'];
    $keywords=$_POST['keywords'];

    $test=$db->query("UPDATE `settings` SET `value` = '$title' WHERE `id` =1, SET `value` = '$description' WHERE `id` =2,SET `value` = '$email' WHERE `id` =3, SET `value` = '$keywords' WHERE `id` =4;");
    if($test){
        echo "good";
    }else{
        echo "bad";
    }

}

UPDATE:

To whoever reading this question now, don't use this structure to store your website settings. and I don't recommend using the query either because it is vulnerable to SQL injection

How Is Putting A Capitol Letter On Every Word Help Readability!? Its Not Cool And Im Sure It Takes You Longer To Type A Sentence.. And Your More Likely TO Make MiStAkeS In Your CamelCase. Ekkkk — Lawrence Cherone, Feb 23 '14 at 16:46
I guess he wrote all in caps and StackOverflow just camelcased the text — arieljuod, Feb 23 '14 at 17:36

score 3 · Answer 1 · answered Feb 23 '14 at 17:31

3

Consider this example...

SELECT * FROM user;
+---------+------+
| user_id | name |
+---------+------+
|       1 | X    |
|       2 | B    |
|       3 | C    |
+---------+------+

UPDATE user 
   SET name = CASE user_id WHEN 1 THEN 'A' 
                           WHEN 2 THEN 'J' 
                           WHEN 3 THEN 'K' END;

SELECT * FROM user;
+---------+------+
| user_id | name |
+---------+------+
|       1 | A    |
|       2 | J    |
|       3 | K    |
+---------+------+

answered Feb 23 '14 at 17:31

Strawberry

33,750
13
40
57

Thank You Bro For Your Help,But This Didn't Work For Me. – Spoody Feb 24 '14 at 11:49
This is my first time seeing this syntax but the documentation on this structure shows END should be END CASE, that may make it functional. – Ralph Ritoch Feb 26 '14 at 09:39
@RalphRitoch Clearly this works. This is the output from my terminal. The structure is as illustrated. – Strawberry Feb 26 '14 at 09:39
@Strawberry, not every version of MySQL supports the same syntax. Regardless I do prefer case syntax over the ON DUPLICATE KEY syntax but I don't know what cross platform issues it may have and the documentation doesn't say what version it became available in. – Ralph Ritoch Feb 26 '14 at 09:44
@Strawberry, I tested this on my server (v5.6.13) and the END CASE syntax that shows in the MySQL manual for MySQL 5.0 throws an error, but using the syntax you gave it does work. Either the documentation is wrong, or my version of MySQL is wrong. – Ralph Ritoch Feb 26 '14 at 09:57
It looks like the syntax changes depending on how it's used, END CASE is for compound statements. – Ralph Ritoch Feb 26 '14 at 10:00
Upvoted this since it is better syntax, even if the syntax changes depending on how it's used. – Ralph Ritoch Feb 26 '14 at 10:05

score 1 · Accepted Answer · answered Feb 23 '14 at 17:08

This query can in fact be done with a single statement in Mysql as long as you have a unique index or primary key on your Id field.

INSERT INTO `settings` (`id`,`value`) VALUES (1,?),(2,?),(3,?),(4,?)
  ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);

See the documentation of INSERT ... ON DUPLICATE KEY at https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Updating one column in multiple rows

2 Answers2