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

User · Answer

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,"'")

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

INSERT INTO tablename      name  salary      VALUES            Bob   1125             Jane   1200             Frank   1100             Susan   1175             John   1150          ON DUPLICATE KEY UPDATE salary   VALUES salary

User · Answer

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 couloumn1   select couloumn1 FROM Table WHERE IDCouloumn    PArent ID         couloumn2   select couloumn2 FROM Table WHERE IDCouloumn    PArent ID         couloumn3   select couloumn3 FROM Table WHERE IDCouloumn    PArent ID         couloumn4   select couloumn4 FROM Table WHERE IDCouloumn    PArent ID     WHERE IDCouloumn IN   List of child Ids

User · Answer

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

User · Answer

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   gt   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   gt   column                  sql      column VALUES  column                 sql      index     len - 1             n                       sql                  return  sql

[mysql] SQL - Update multiple records in one query

Examples related to mysql

Examples related to record