Rectangle 27 0

sql Multiple Updates in MySQL?


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

@HaralanDobrev Using INSERT IGNORE still inserts the non duplicated records. which Jayapal wanted to avoid. INSERT IGNORE just turns any errors into warning :( stackoverflow.com/questions/548541/

@JayapalChandran you should use INSERT IGNORE together with ON DUPLICATE KEY UPDATE. dev.mysql.com/doc/refman/5.5/en/insert.html

If there is no duplicates then i dont want that row to be inserted. what should id do? because i am fetching information from another site which maintains tables with id's. I am inserting values with respect to that id. if the site has new records then i will end up inserting only the ids and count except all other information. if and only if there is an entry for the id then it should update else it should skip. what shall i do?

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


$commandTxt = 'UPDATE operations SET chunk_finished = CASE id ';                   foreach ($blockOperationChecked as $operationID => $operationChecked)                     $commandTxt .= " WHEN $operationID THEN $operationChecked ";                                               $commandTxt .= 'ELSE id                                  END                                 WHERE id IN ('.implode(', ', array_keys(blockOperationChecked )).');';

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.

Thanks ! it was very helpful .

maybe not so pretty to write for dynamic updating but interesting look at the functionality of casing...

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


// Reorder items
function update_items_tempdb(&$items)
{
    shuffle($items);
    $table_name = uniqid('tmp_test_');
    $sql = "CREATE TEMPORARY TABLE `$table_name` ("
        ."  `id` int(10) unsigned NOT NULL AUTO_INCREMENT"
        .", `position` int(10) unsigned NOT NULL"
        .", PRIMARY KEY (`id`)"
        .") ENGINE = MEMORY";
    query($sql);
    $i = 0;
    $sql = '';
    foreach ($items as &$item)
    {
        $item->position = $i++;
        $sql .= ($sql ? ', ' : '')."({$item->id}, {$item->position})";
    }
    if ($sql)
    {
        query("INSERT INTO `$table_name` (id, position) VALUES $sql");
        $sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position"
            ." WHERE `$table_name`.id = `test`.id";
        query($sql);
    }
    query("DROP TABLE `$table_name`");
}

Use a temporary table

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


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;

I have tried this type of query. But when records reach 30k Boundary server stopped. Is there any other solution?

Not sure why another useful option is not yet mentioned:

This is the best. Especially if you are pulling the values for update from another SQL query as i was doing.

This was great for an update on a table with a massive amount of columns. I will probably be using this query a lot in the future. Thanks!

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


<?
//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<3;$i++)
    RunTest($i, $NumRows);

function RunTest($TestNum, $NumRows)
{
    $TheQueries=Array();
    $DoQuery=function($Query) use (&$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<=$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<=$NumRows;$i++)
            $Query[]=sprintf("(%d,%d)", $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<=$NumRows;$i++)
            $Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
    }

    print "$TestName: ".(microtime(true)-$Start)."<br>\n";

    file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}
SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES","")
sql_mode
  • CASE: In which you a case/when for each different record within an UPDATE
  • INSERT: INSERT with ON DUPLICATE KEY UPDATE
  • TRANSACTION: Where you do an update for each record within a transaction

All of the following applies to InnoDB.

As for other comments I've seen that say the auto_increment goes up using the INSERT method, I tested that too and it seems to not be the case.

Code to run the tests is as follows. It also outputs .SQL files to remove php interpreter overhead

I feel knowing the speeds of the 3 different methods is important.

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.

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.

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.

There are 3 methods:

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


  • 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.

@Dakusan, great answer. Thanks a lot for extending, commenting and fixing my results.

Could someone provide a link to what transactions to do this look like ? And/or code for an injection-safe code for the variant with case statement ?

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.

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:

The question is old, yet I'd like to extend the topic with another answer.

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


$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->multi_query($query);
$con->close();
COMMIT
START TRANSACTION
mysql::commit(..)
mysqli::begin_transaction(..)

How to wrap them in one transaction? Show us, please.

If you're using PHP, you can use mysqli to do multi statements (I think php has shipped with mysqli for a while now)

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.

This is the same as sending the queries separately. The only difference is that you send it all in one network packet, but the UPDATEs will be still processed as separate queries. Better is to wrap them in one transaction, then the changes will be commited to the table at once.

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

There is a reference in the MySQL manual for multiple tables.

This should work for ya.

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


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

@HaralanDobrev Using INSERT IGNORE still inserts the non duplicated records. which Jayapal wanted to avoid. INSERT IGNORE just turns any errors into warning :( stackoverflow.com/questions/548541/

@JayapalChandran you should use INSERT IGNORE together with ON DUPLICATE KEY UPDATE. dev.mysql.com/doc/refman/5.5/en/insert.html

If there is no duplicates then i dont want that row to be inserted. what should id do? because i am fetching information from another site which maintains tables with id's. I am inserting values with respect to that id. if the site has new records then i will end up inserting only the ids and count except all other information. if and only if there is an entry for the id then it should update else it should skip. what shall i do?

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

Note
Rectangle 27 0

sql Multiple Updates in MySQL?


// Reorder items
function update_items_tempdb(&$items)
{
    shuffle($items);
    $table_name = uniqid('tmp_test_');
    $sql = "CREATE TEMPORARY TABLE `$table_name` ("
        ."  `id` int(10) unsigned NOT NULL AUTO_INCREMENT"
        .", `position` int(10) unsigned NOT NULL"
        .", PRIMARY KEY (`id`)"
        .") ENGINE = MEMORY";
    query($sql);
    $i = 0;
    $sql = '';
    foreach ($items as &$item)
    {
        $item->position = $i++;
        $sql .= ($sql ? ', ' : '')."({$item->id}, {$item->position})";
    }
    if ($sql)
    {
        query("INSERT INTO `$table_name` (id, position) VALUES $sql");
        $sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position"
            ." WHERE `$table_name`.id = `test`.id";
        query($sql);
    }
    query("DROP TABLE `$table_name`");
}

Use a temporary table

Note