SQL - Update multiple records in one query

Question

I have table - config. Schema: config_name | config_value

And I would like to update multiple records in one query. I try like that:

UPDATE config 
SET t1.config_value = 'value'
  , t2.config_value = 'value2' 
WHERE t1.config_name = 'name1' 
  AND t2.config_name = 'name2';

but that query is wrong :(

Can you help me?

Answer 1

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is SQLFiddle demo

Answer 2

in my case I have to update the records which are more than 1000, for this instead of hitting the update query each time I preferred this,

   UPDATE mst_users 
   SET base_id = CASE user_id 
   WHEN 78 THEN 999 
   WHEN 77 THEN 88 
   ELSE base_id END WHERE user_id IN(78, 77)

78,77 are the user Ids and for those user id I need to update the base_id 999 and 88 respectively.This works for me.

Answer 3

Camille's solution worked. Turned it into a basic PHP function, which writes up the SQL statement. Hope this helps someone else.

    function _bulk_sql_update_query($table, $array)
    {
        /*
         * Example:
        INSERT INTO mytable (id, a, b, c)
        VALUES (1, 'a1', 'b1', 'c1'),
        (2, 'a2', 'b2', 'c2'),
        (3, 'a3', 'b3', 'c3'),
        (4, 'a4', 'b4', 'c4'),
        (5, 'a5', 'b5', 'c5'),
        (6, 'a6', 'b6', 'c6')
        ON DUPLICATE KEY UPDATE id=VALUES(id),
        a=VALUES(a),
        b=VALUES(b),
        c=VALUES(c);
    */
        $sql = "";

        $columns = array_keys($array[0]);
        $columns_as_string = implode(', ', $columns);

        $sql .= "
      INSERT INTO $table
      (" . $columns_as_string . ")
      VALUES ";

        $len = count($array);
        foreach ($array as $index => $values) {
            $sql .= '("';
            $sql .= implode('", "', $array[$index]) . "\"";
            $sql .= ')';
            $sql .= ($index == $len - 1) ? "" : ", \n";
        }

        $sql .= "\nON DUPLICATE KEY UPDATE \n";

        $len = count($columns);
        foreach ($columns as $index => $column) {

            $sql .= "$column=VALUES($column)";
            $sql .= ($index == $len - 1) ? "" : ", \n";
        }

        $sql .= ";";

        return $sql;
    }

Answer 4

You can accomplish it with INSERT as below:

INSERT INTO mytable (id, a, b, c)
VALUES (1, 'a1', 'b1', 'c1'),
(2, 'a2', 'b2', 'c2'),
(3, 'a3', 'b3', 'c3'),
(4, 'a4', 'b4', 'c4'),
(5, 'a5', 'b5', 'c5'),
(6, 'a6', 'b6', 'c6')
ON DUPLICATE KEY UPDATE id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c);

This insert new values into table, but if primary key is duplicated (already inserted into table) that values you specify would be updated and same record would not be inserted second time.

Answer 5

maybe someone it will be useful

for Postgresql 9.5 works as a charm

INSERT INTO tabelname(id, col2, col3, col4)
VALUES
    (1, 1, 1, 'text for col4'),
    (DEFAULT,1,4,'another text for col4')
ON CONFLICT (id) DO UPDATE SET
    col2 = EXCLUDED.col2,
    col3 = EXCLUDED.col3,
    col4 = EXCLUDED.col4

this SQL update existing record and insert new (2 in one)

Answer 6

Execute the below code if you want to update all record in all columns:

update config set column1='value',column2='value'...columnN='value';

and if you want to update all columns of a particular row then execute below code:

update config set column1='value',column2='value'...columnN='value' where column1='value'