Updating Multiple Rows @ Once

**Caldus** · Jun 22, 2003 13:26

How would I use one UPDATE query to update every row with different values? Like so:

Code:

UPDATE table SET column = 'value1' WHERE id = 1
UPDATE table SET column = 'value2' WHERE id = 2
UPDATE table SET column = 'value3' WHERE id = 3

And so on...

How would I acheive this with using one query? Or is this not possible? Thanks.

**Caldus** · Jun 23, 2003 15:16

Anyone?

**website** · Jun 23, 2003 17:05

I've been wondering about the exact same thing but I think you can't do it, I mean, how would you update many rows that hold many different values with one query, this just doesn't make sense if you really think about it.

I could be awfully wrong though

**Zoef** · Jun 23, 2003 17:27

Originally Posted by website

I could be awfully wrong though

I don't think you are.

What's perfectly possible of course is to update several rows with the same value.

Rik

**Caldus** · Jun 24, 2003 16:14

Originally Posted by Zoef

I don't think you are.

What's perfectly possible of course is to update several rows with the same value.

Rik

Hmmm, guess I'll have to do 50 queries then. : \

**website** · Jun 24, 2003 16:30

if the value follows some pattern you *could* place it in some loop ...

**siteguru** · Jun 25, 2003 06:44

Which would still be 50 queries, just not 50 separate SQL queries to type out.

**MattR** · Jun 25, 2003 07:17

I don't know if this will work in MySQL, but:

Code:

UPDATE table SET col1 = CASE id
                          WHEN 1
                            THEN 'foo1'
                          WHEN 2
                            THEN 'foo2'
                        END

**Zoef** · Jun 25, 2003 12:56

Originally Posted by MattR

I don't know if this will work in MySQL, but:

Code:

UPDATE table SET col1 = CASE id
                          WHEN 1
                            THEN 'foo1'
                          WHEN 2
                            THEN 'foo2'
                        END

I guess it just might, 'CASE' is mentioned in the manual:
http://www.mysql.com/documentation/m...flow_functions

Rik