Multiple Updates in MySQL

Question

I know that you can insert multiple rows at once  is there a way to update multiple rows at once  as in  in one query  in MySQL   Edit  For example I have the following  Name   id  Col1  Col2 Row1   1    6     1 Row2   2    2     3 Row3   3    9     5 Row4   4    16    8   I want to combine all the following Updates into one query  UPDATE table SET Col1   1 WHERE id   1  UPDATE table SET Col1   2 WHERE id   2  UPDATE table SET Col2   3 WHERE id   3  UPDATE table SET Col1   10 WHERE id   4  UPDATE table SET Col2   12 WHERE id   4

User · Answer

I took the answer from  newtover and extended it using the new json table function in MySql 8  This allows you to create a stored procedure to handle the workload rather than building your own SQL text in code  drop table if exists  test   create table  test       Id  int     Number  int    PRIMARY KEY   Id      insert into test  Id  Number  values  1  1    2  2    DROP procedure IF EXISTS  Test   DELIMITER    CREATE PROCEDURE  Test       p json json   BEGIN     update test s         join json table p json         columns  id  int path    id    number  int path    number    v          on s Id v id set s Number v number  END   DELIMITER    call  Test      quot id quot   1   quot number quot   10     quot id quot   2   quot number quot   20      select   from test   drop table if exists  test    It s a few ms slower than pure SQL but I m happy to take the hit rather than generate the sql text in code  Not sure how performant it is with huge recordsets  the JSON object has a max size of 1Gb  but I use it all the time when updating 10k rows at a time

User · Answer

Yes  that s possible - you can use INSERT     ON DUPLICATE KEY UPDATE   Using your example   INSERT INTO table  id Col1 Col2  VALUES  1 1 1   2 2 3   3 9 3   4 10 12  ON DUPLICATE KEY UPDATE Col1 VALUES Col1  Col2 VALUES Col2

User · Answer

The following will update all rows in one table  Update Table Set Column1    New Value    The next one will update all rows where the value of Column2 is more than 5  Update Table Set Column1    New Value  Where Column2  gt  5   There is all Unkwntech s example of updating more than one table  UPDATE table1  table2 SET table1 col1    value   table2 col1    value  WHERE table1 col3    567  AND table2 col6  567

User · Answer

Yes   it is possible using INSERT ON DUPLICATE KEY UPDATE sql statement   syntax  INSERT INTO table name  a b c  VALUES  1 2 3   4 5 6      ON DUPLICATE KEY UPDATE a VALUES a  b VALUES b  c VALUES c

User · Answer

Since you have dynamic values  you need to use an IF or CASE for the columns to be updated   It gets kinda ugly  but it should work   Using your example  you could do it like    UPDATE table SET Col1   CASE id                            WHEN 1 THEN 1                            WHEN 2 THEN 2                            WHEN 4 THEN 10                            ELSE Col1                          END                    Col2   CASE id                            WHEN 3 THEN 3                            WHEN 4 THEN 12                            ELSE Col2                          END              WHERE id IN  1  2  3  4

User · Answer

You may also be interested in using joins on updates  which is possible as well   Update someTable Set someValue   4 From someTable s Inner Join anotherTable a on s id   a id Where a id   4 -- Only updates someValue in someTable who has a foreign key on anotherTable with a value of 4    Edit  If the values you are updating aren t coming from somewhere else in the database  you ll need to issue multiple update queries

User · Answer

UPDATE  your table  SET    something    IF  id   1   new value1   something     smth2    IF  id   1    nv1   smth2     something    IF  id   2   new value2   something     smth2    IF  id   2    nv2   smth2     something    IF  id   4   new value3   something     smth2    IF  id   4    nv3   smth2     something    IF  id   6   new value4   something     smth2    IF  id   6    nv4   smth2     something    IF  id   3   new value5   something     smth2    IF  id   3    nv5   smth2     something    IF  id   5   new value6   something     smth2    IF  id   5    nv6   smth2          You just building it in php like   q    UPDATE  your table  SET     foreach  data as  dat       q               something    IF  id      dat- gt id        dat- gt value     something             smth2    IF  id      dat- gt id         dat- gt value2     smth2           q   substr  q 0 -1     So you can update hole table with one query

User · Answer

UPDATE tableName SET col1  000  WHERE id  3  OR id  5    This should achieve what you r looking for   Just add more id s   I have tested it

User · Answer

You can alias the same table to give you the id s you want to insert by  if you are doing a row-by-row update   UPDATE table1 tab1  table1 tab2 -- alias references the same table SET  col1   1  col2   2        WHERE  tab1 id   tab2 id    Additionally  It should seem obvious that you can also update from other tables as well  In this case  the update doubles as a  SELECT  statement  giving you the data from the table you are specifying  You are explicitly stating in your query the update values so  the second table is unaffected

User · Answer

Not sure why another useful option is not yet mentioned   UPDATE my table m JOIN       SELECT 1 as id  10 as  col1  20 as  col2     UNION ALL     SELECT 2  5  10     UNION ALL     SELECT 3  15  30   vals ON m id   vals id SET col1    col1  col2    col2

User · Answer

And now the easy way update my table m  -- let create a temp table with populated values      select 1 as id  20 as value union -- this part will be generated      select 2 as id  30 as value union -- using a backend code      -- for loop       select N as id  X as value           t set m value   t value where t id m id -- now update by join - quick

User · Answer

Use a temporary table    Reorder items function update items tempdb  amp  items        shuffle  items        table name   uniqid  tmp test          sql    quot CREATE TEMPORARY TABLE   table name    quot            quot    id  int 10  unsigned NOT NULL AUTO INCREMENT quot            quot    position  int 10  unsigned NOT NULL quot            quot   PRIMARY KEY   id   quot            quot   ENGINE   MEMORY quot       query  sql        i   0       sql           foreach   items as  amp  item                 item- gt position    i             sql      sql               quot    item- gt id     item- gt position   quot             if   sql                query  quot INSERT INTO   table name   id  position  VALUES  sql quot             sql    quot UPDATE  test     table name  SET  test  position     table name  position quot                quot  WHERE   table name  id    test  id quot           query  sql             query  quot DROP TABLE   table name  quot

User · Answer

Why does no one mention multiple statements in one query   In php  you use multi query method of mysqli instance   From the php manual     MySQL optionally allows having multiple statements in one statement string  Sending multiple statements at once reduces client-server round trips but requires special handling    Here is the result comparing to other 3 methods in update 30 000 raw  Code can be found here which is based on answer from  Dakusan   Transaction  5 5194580554962 Insert  0 20669293403625 Case  16 474853992462 Multi  0 0412278175354  As you can see  multiple statements query is more efficient than the highest answer   If you get error message like this   PHP Warning   Error while sending SET OPTION packet   You may need to increase the max allowed packet in mysql config file which in my machine is  etc mysql my cnf and then restart mysqld

User · Answer

There is a setting you can alter called  multi statement  that disables MySQL s  safety mechanism  implemented to prevent  more than one  injection command  Typical to MySQL s  brilliant  implementation  it also prevents user from doing efficient queries   Here  http   dev mysql com doc refman 5 1 en mysql-set-server-option html  is some info on the C implementation of the setting   If you re using PHP  you can use mysqli to do multi statements  I think php has shipped with mysqli for a while now    con   new mysqli  localhost   user1   password   my database     query    Update MyTable SET col1  some value  WHERE id 1 LIMIT 1     query     UPDATE MyTable SET col1  other value  WHERE id 2 LIMIT 1      etc  con- gt multi query  query    con- gt close      Hope that helps

User · Answer

use  REPLACE INTO table  VALUES   id   col1   col2   VALUES  1 6 1   2 2 3   3 9 5   4 16 8     Please note    id has to be a primary unique key  if you use foreign keys to reference the table  REPLACE deletes then inserts  so this might cause an error

User · Answer

UPDATE table1  table2 SET table1 col1  value   table2 col1  value  WHERE table1 col3  567  AND table2 col6  567    This should work for ya   There is a reference in the MySQL manual for multiple tables

User · Answer

The question is old  yet I d like to extend the topic with another answer   My point is  the easiest way to achieve it is just to wrap multiple queries with a transaction  The accepted answer INSERT     ON DUPLICATE KEY UPDATE is a nice hack  but one should be aware of its drawbacks and limitations    As being said  if you happen to launch the query with rows whose primary keys don t exist in the table  the query inserts new  half-baked  records  Probably it s not what you want If you have a table with a not null field without default value and don t want to touch this field in the query  you ll get  Field  fieldname  doesn t have a default value  MySQL warning even if you don t insert a single row at all  It will get you into trouble  if you decide to be strict and turn mysql warnings into runtime exceptions in your app    I made some performance tests for three of suggested variants  including the INSERT     ON DUPLICATE KEY UPDATE variant  a variant with  case   when   then  clause and a naive approach with transaction  You may get the python code and results here  The overall conclusion is that the variant with case statement turns out to be twice as fast as two other variants  but it s quite hard to write correct and injection-safe code for it  so I personally stick to the simplest approach  using transactions   Edit  Findings of Dakusan prove that my performance estimations are not quite valid  Please see this answer for another  more elaborate research

User · Answer

All of the following applies to InnoDB  I feel knowing the speeds of the 3 different methods is important  There are 3 methods   INSERT  INSERT with ON DUPLICATE KEY UPDATE TRANSACTION  Where you do an update for each record within a transaction CASE  In which you a case when for each different record within an UPDATE  I just tested this  and the INSERT method was 6 7x faster for me than the TRANSACTION method  I tried on a set of both 3 000 and 30 000 rows  The TRANSACTION method still has to run each individually query  which takes time  though it batches the results in memory  or something  while executing  The TRANSACTION method is also pretty expensive in both replication and query logs  Even worse  the CASE method was 41 1x slower than the INSERT method w  30 000 records  6 1x slower than TRANSACTION   And 75x slower in MyISAM  INSERT and CASE methods broke even at  1 000 records  Even at 100 records  the CASE method is BARELY faster  So in general  I feel the INSERT method is both best and easiest to use  The queries are smaller and easier to read and only take up 1 query of action  This applies to both InnoDB and MyISAM  Bonus stuff  The solution for the INSERT non-default-field problem is to temporarily turn off the relevant SQL modes  SET SESSION sql mode REPLACE REPLACE   SESSION sql mode  quot STRICT TRANS TABLES quot   quot  quot    quot STRICT ALL TABLES quot   quot  quot    Make sure to save the sql mode first if you plan on reverting it  As for other comments I ve seen that say the auto increment goes up using the INSERT method  this does seem to be the case in InnoDB  but not MyISAM  Code to run the tests is as follows  It also outputs  SQL files to remove php interpreter overhead  lt  php   Variables  NumRows 30000     These 2 functions need to be filled in function InitSQL        function RunSQLQuery  Q          Run the 3 tests InitSQL    for  i 0  i lt 3  i        RunTest  i   NumRows    function RunTest  TestNum   NumRows         TheQueries Array         DoQuery function  Query  use   amp  TheQueries                RunSQLQuery  Query            TheQueries    Query               TableName  Test        DoQuery  DROP TABLE IF EXISTS    TableName        DoQuery  CREATE TABLE    TableName    i1 int NOT NULL AUTO INCREMENT  i2 int NOT NULL  primary key  i1   ENGINE InnoDB         DoQuery  INSERT INTO    TableName    i2  VALUES    implode         range 2   NumRows 1              if  TestNum  0                 TestName  Transaction            Start microtime true            DoQuery  START TRANSACTION            for  i 1  i lt   NumRows  i                 DoQuery  UPDATE    TableName   SET i2      i 5  1000    WHERE i1    i            DoQuery  COMMIT                   if  TestNum  1                 TestName  Insert            Query Array            for  i 1  i lt   NumRows  i                 Query   sprintf  quot   d  d  quot    i     i 5  1000             Start microtime true            DoQuery  INSERT INTO    TableName   VALUES   implode        Query    ON DUPLICATE KEY UPDATE i2 VALUES i2                    if  TestNum  2                 TestName  Case            Query Array            for  i 1  i lt   NumRows  i                 Query   sprintf  WHEN  d THEN  d    i     i 5  1000             Start microtime true            DoQuery  quot UPDATE  TableName SET i2 CASE i1 n quot  implode  quot  n quot    Query   quot  nEND nWHERE i1 IN   quot  implode      range 1   NumRows                        print  quot  TestName   quot   microtime true - Start   quot  lt br gt  n quot        file put contents  quot    TestName sql quot   implode  quot   n quot    TheQueries

[mysql] Multiple Updates in MySQL

Examples related to mysql

Examples related to sql

Examples related to sql-update