Update multiple rows in a single MySQL query

Question

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?

Possible duplicate of [Multiple Updates in MySQL](http://stackoverflow.com/questions/3432/multiple-updates-in-mysql). You'll find your answer there — Itay, Sep 14 '13 at 14:16
yeah i know that solution, but in my case, every rows will already exist in table(it doesnt need to be inserted), so it would always run ON DUPLICATE KEY UPDATE and make everything a lot slower. — user2618929, Sep 14 '13 at 14:20
I can't understand what do you mean. Use the `INSERT` command when you need to update and that's all. If you use a primary key it won't be much slower — Itay, Sep 14 '13 at 14:21
ok, i will do that if nobody will suggest better solution, thank you — user2618929, Sep 14 '13 at 14:24
Just to make it clear, if you use your primary key (not just col1 = "test") it will be pretty fast. — Itay, Sep 14 '13 at 14:25

score 40 · Answer 1 · edited Mar 18 '14 at 22:46

40

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

edited Mar 18 '14 at 22:46

Farnabaz

4,030
1
22
42

answered Mar 18 '14 at 22:27

user3435275

417
4
4

1

Isn't the WHERE-part unnecessary? – Clox Feb 08 '15 at 13:46
Without the WHERE, the CASE will fall thru returning null (probably not what anyone wants). I believe adding an "ELSE col2" is safeish without the WHERE, but will lead to uncessary updates. – c0dem4gnetic Sep 18 '15 at 11:59
@Clox also, if you don't include the `WHERE` clause then mysql has to go through **each** row to check. Sure there will be no performance issue with this example but imagine if you have 1000 rows and you only want to update 2 rows. – Fahmi Sep 01 '16 at 16:32

score 4 · Answer 2 · answered Mar 03 '15 at 21:11

4

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.

answered Mar 03 '15 at 21:11

Ruud H.G. van Tol

51
1

score 3 · Answer 3 · answered Sep 14 '13 at 14:16

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

answered Sep 14 '13 at 14:16

John Woo

258,903
69
498
492

i would like to add a lot more rows in future and with more columns :( I just gave simple example to my problem. – user2618929 Sep 14 '13 at 14:17
@Itay what do you mean by flexible? – John Woo Sep 14 '13 at 14:17
I mean that it will be very hard to update 100 rows like that. – Itay Sep 14 '13 at 14:18
@user2618929 then build a dynamic sql. – John Woo Sep 14 '13 at 14:19
@Itay so can you provide an alternative? – John Woo Sep 14 '13 at 14:19
See the duplicated post for a better solution – Itay Sep 14 '13 at 14:19
@Itay where is the duplicate post ? – Anirudha Gupta Jun 19 '14 at 12:23

score 1 · Answer 4 · answered Sep 14 '13 at 14:19

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.

score 0 · Answer 5 · answered Nov 25 '17 at 14:26

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'

score 0 · Answer 6 · answered May 16 '22 at 16:07

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

Update multiple rows in a single MySQL query

6 Answers6

Linked

Related