'Update multiple rows in a single MySQL query

I am trying to run this:

UPDATE test 
SET col2=1 WHERE col1='test1', 
SET col2=3 WHERE col1='test2';

The error I am getting:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

My table:

CREATE TABLE `test` (
    `col1` varchar(30) NOT NULL,
    `col2` int(5) DEFAULT NULL,
    PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It’s something about , at the end of the first row. When I changed it to ;, it didn’t recognize col2. How can I do this in one query?

mysqlsql


Solution 1:[1]

This is most clear way

UPDATE test
SET col2 = CASE col1
WHEN 'test1' THEN 1
WHEN 'test2' THEN 3
WHEN 'test3' THEN 5
END,
colx = CASE col1
WHEN 'test1' THEN 'xx'
WHEN 'test2' THEN 'yy'
WHEN 'test3' THEN 'zz'
END
WHERE col1 IN ('test1','test2','test3')

Solution 2:[2]

Consider using INSERT-ODKU (ON DUPLICATE KEY UPDATE), because that supports to update multiple rows.

Make sure that the values of all PK columns are in the VALUES().

Where feasible, generate the SQL with data from a slave.

Solution 3:[3]

you can use CASE on this

UPDATE test 
SET col2 = CASE WHEN col1 = 'test1' THEN 1 ELSE 3 END 
WHERE col1 IN ('test1', 'test2')

or IF (for MySQL only)

UPDATE test 
SET col2 = IF(col1 = 'test1', 1, 3)
WHERE col1 IN ('test1', 'test2')

Solution 4:[4]

alternatively when the construct with cases gets too unreadable, you could/should start a transaction and just do the updates sequentially.

this usually results in more straightforward sql, except if the first statements creates rows that then are matched by the second statement when they should not. however this is not the case in your example.

Solution 5:[5]

This is how I did it:

UPDATE col1 (static value), col2 (static value), and col3 (different values) WHERE col4 has different values AND col5 is static.

$someArray = ["a","b","c"];
$anotherArray = [1,2,3];

$sql = "UPDATE table SET col1 = '$staticValue1', col2 = '$staticValue2', col3 = CASE col4";
    $sqlEnd = " END WHERE col4 IN (";
    $seperator = ",";
    for ( $c = 0; $c < count($someArray); $c++ ) {
       $sql .= " WHEN " . "'" . $someArray[$c] . "'" . " THEN " . $anotherArray[$c];
       if ( $c === count($someArray) - 1 ) { 
          $separator = ") AND col5 = '$staticValue5'";
       }
        $sqlEnd .= "'" . $someArray[$c] . "'" . $seperator;

    }
    $sql .= $sqlEnd;
    $retval = mysqli_query( $conn, $sql);
    if(! $retval ) {
        /* handle error here */
    }

And the output string for MySql query would be something like this:

UPDATE table SET col1 = '1', col2 = '2', col3 = CASE col4 WHEN 'a' THEN 1 WHEN 'b' THEN 2 WHEN 'c' THEN 3 END WHERE col4 IN ('a','b','c') AND col5 = 'col5'

Solution 6:[6]

With MySQL8 it can be done smarter, but also consider creating a temporary result set, inside the SQL, to JOIN with:

UPDATE MyTable t JOIN (
SELECT 'v1a' AS c1, 'v1b' AS c2 
UNION ALL SELECT 'v2a', 'v2b' 
UNION ALL SELECT 'v3a', 'v3b'
) AS u USING (c1) 
SET t.c2 = u.c2

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Farnabaz
Solution 2 Ruud H.G. van Tol
Solution 3 John Woo
Solution 4 mnagel
Solution 5 jdrake
Solution 6 druud62

Related Questions