CODEWITHSUNDEEP
mysqlrecord
user3022527
user3022527

Reputation: 2187

SQL - Update multiple records in one query

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?

Upvotes: 205

Views: 1078486

Answers (18)

Jon Hulka
Jon Hulka

Reputation: 1319

In MySQL 8.0 or later you can do this with JSON Tables:

UPDATE config
JOIN JSON_TABLE(
    '[
        {"config_name":"name1","config_value":"value"},
        {"config_name":"name2","config_value":"value2"}                
    ]',
    '$[*]'
    COLUMNS(
        config_name varchar(45) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci PATH '$.config_name',
        config_value varchar(100) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci PATH '$.config_value'
    )
) config_values ON config.config_name = config_values.config_name
SET config.config_value = config_values.config_value;

Upvotes: 0

John Muraguri
John Muraguri

Reputation: 504

I have found that using ON DUPLICATE KEY UPDATE on MySQL causes the auto increment id to skip values, which may be acceptable. I prefer to use this approach instead.

CREATE TABLE config
    (`config_name` varchar(255), `config_value` varchar(255))
;
    
INSERT INTO config
    (`config_name`, `config_value`)
VALUES
    ('name1', NULL),
    ('name2', NULL),
    ('name3', 'value3'),
    ('name4', 'value4'),
    ('name5', 'value5')
;

UPDATE config
JOIN (select 'name1' as config_name, 'value1' as config_value
     union all
     select 'name2', 'value2'
     union all
     select 'name4', 'updated value4'
     union all
     select 'name5', 'updated value5'
     ) x on x.config_name = config.config_name
   SET config.config_value = x.config_value;

Alternatively, you can use temporary tables.

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `foo` (`id`, `bar`) VALUES (1, 'a'), (2, 'b');
CREATE TEMPORARY TABLE `foo_tmp` LIKE `foo`;
INSERT INTO `foo_tmp` (`id`, `bar`) VALUES (1, 'c'), (2, 'd');
UPDATE `foo` f INNER JOIN foo_tmp ft ON f.id = ft.id SET f.bar = ft.bar;

Upvotes: 0

Tom
Tom

Reputation: 413

There have two another way :

one:

UPDATE `table_name`                                                             
SET                                                                
       `desc`   = CASE                                    
                    WHEN (`id_one`='1' AND `id_tow`='1') THEN '22222'                                                               
                    END                                                          
WHERE  (`id_one`, `id_tow`) IN ( ('1', '1') );

two

 UPDATE table_name
   SET field_to_update = CASE concat(key1, key2) 
                WHEN concat(1,1) THEN field_value1 
                WHEN concat(2,1) THEN feild_value2 
                ELSE feild_to_update
                END
   WHERE concat(key1, key2) IN ( concat(1, 1) , concat(2, 1) );

Upvotes: 0

T.H.
T.H.

Reputation: 876

If you need to update several rows at a time, the alternative is prepared statement:

  • database complies a query pattern you provide the first time, keep the compiled result for current connection (depends on implementation).
  • then you updates all the rows, by sending shortened label of the prepared function with different parameters in SQL syntax, instead of sending entire UPDATE statement several times for several updates
  • the database parse the shortened label of the prepared function , which is linked to the pre-compiled result, then perform the updates.
  • next time when you perform row updates, the database may still use the pre-compiled result and quickly complete the operations (so the first step above can be omitted since it may take time to compile).

Here is PostgreSQL example of prepare statement, many of SQL databases (e.g. MariaDB,MySQL, Oracle) also support it.

Upvotes: 0

user20516567
user20516567

Reputation: 1

UPDATE table name SET field name = 'value' WHERE table name.primary key

Upvotes: 0

peterm
peterm

Reputation: 92815

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 a 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 a SQLFiddle demo

Upvotes: 235

PrWhite
PrWhite

Reputation: 19

just make a transaction statement, with multiple update statement and commit. In error case, you can just rollback modification handle by starting transaction.

START TRANSACTION;
/*Multiple update statement*/
COMMIT;

(This syntax is for MySQL, for PostgreSQL, replace 'START TRANSACTION' by 'BEGIN')

Upvotes: 1

EliteRaceElephant
EliteRaceElephant

Reputation: 8162

UPDATE 2021 / MySql v8.0.20 and later

The most upvoted answer advises to use the VALUES function which is now DEPRECATED for the ON DUPLICATE KEY UPDATE syntax. With v8.0.20 you get a deprecation warning with the VALUES function:

INSERT INTO chart (id, flag)
VALUES (1, 'FLAG_1'),(2, 'FLAG_2')
ON DUPLICATE KEY UPDATE id = VALUES(id), flag = VALUES(flag);

[HY000][1287] 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

Use the new alias syntax instead:

INSERT INTO chart (id, flag) 
VALUES (1, 'FLAG_1'),(2, 'FLAG_2') AS aliased
ON DUPLICATE KEY UPDATE flag=aliased.flag;

Upvotes: 5

Hiren Savsani
Hiren Savsani

Reputation: 1

Try either multi-table update syntax

Try it copy and SQL query:

CREATE TABLE #temp (id int, name varchar(50))
CREATE TABLE #temp2 (id int, name varchar(50))

INSERT INTO #temp (id, name)
VALUES (1,'abc'), (2,'xyz'), (3,'mno'), (4,'abc')

INSERT INTO #temp2 (id, name) 
VALUES (2,'def'), (1,'mno1')

SELECT * FROM #temp
SELECT * FROM #temp2

UPDATE t
SET name = CASE WHEN t.id = t1.id THEN t1.name ELSE t.name END
FROM #temp t 
INNER JOIN #temp2 t1 on t.id = t1.id
 
select * from #temp
select * from #temp2

drop table #temp
drop table #temp2

Upvotes: 0

Harrish Selvarajah
Harrish Selvarajah

Reputation: 1873

Execute the code below to update n number of rows, where Parent ID is the id you want to get the data from and Child ids are the ids u need to be updated so it's just u need to add the parent id and child ids to update all the rows u need using a small script.

 UPDATE [Table]
 SET column1 = (SELECT column1 FROM Table WHERE IDColumn = [PArent ID]),
     column2 = (SELECT column2 FROM Table WHERE IDColumn = [PArent ID]),
     column3 = (SELECT column3 FROM Table WHERE IDColumn = [PArent ID]),
     column4 = (SELECT column4 FROM Table WHERE IDColumn = [PArent ID]),
 WHERE IDColumn IN ([List of child Ids])

Upvotes: 6

jay10
jay10

Reputation: 51

INSERT INTO tablename
    (name, salary)
    VALUES 
        ('Bob', 1125),
        ('Jane', 1200),
        ('Frank', 1100),
        ('Susan', 1175),
        ('John', 1150)
        ON DUPLICATE KEY UPDATE salary = VALUES(salary);

Upvotes: 3

Shuhad zaman
Shuhad zaman

Reputation: 3390

instead of this 

UPDATE staff SET salary = 1200 WHERE name = 'Bob';
UPDATE staff SET salary = 1200 WHERE name = 'Jane';
UPDATE staff SET salary = 1200 WHERE name = 'Frank';
UPDATE staff SET salary = 1200 WHERE name = 'Susan';
UPDATE staff SET salary = 1200 WHERE name = 'John';

you can use 

UPDATE staff SET salary = 1200 WHERE name IN ('Bob', 'Frank', 'John');

Upvotes: 33

Ivar
Ivar

Reputation: 41

Assuming you have the list of values to update in an Excel spreadsheet with config_value in column A1 and config_name in B1 you can easily write up the query there using an Excel formula like

=CONCAT("UPDATE config SET config_value = ","'",A1,"'", " WHERE config_name = ","'",B1,"'")

Upvotes: 4

Oleh Sobchuk
Oleh Sobchuk

Reputation: 3722

maybe for 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 updates existing record and inserts if new one (2 in 1)

Upvotes: 12

adamk
adamk

Reputation: 380

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;
    }

Upvotes: 9

Jason Clark
Jason Clark

Reputation: 1423

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'

Upvotes: 5

vaibhav kulkarni
vaibhav kulkarni

Reputation: 1810

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.

Upvotes: 34

camille khalaghi
camille khalaghi

Reputation: 2239

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.

Upvotes: 205

Related Questions