mySQL - 如何在一个查询中进行多次更新？ [重复]

Question

This question already has an answer here:

• MySQL - UPDATE multiple rows with different values in one query 4 answers

I have 2 update queries, same tables but different rows and values. Since its same table, making 2 queries is an unprofessional way.

UPDATE members SET age="18" WHERE nick = "John"
UPDATE members SET height="180" WHERE nick = "Stuart"

Is there a better (1 query) method for this ? Thanks

</div>

Answer 1

Just use the right where clause:

UPDATE members
    SET age = 18
WHERE nick IN ('John', 'Stuart')

I am guessing that age is an integer. I should also point out that storing "age" in a database is very uncommon -- because it is a bad idea. Every day, people's ages change.

EDIT:

UPDATE members
    SET age = (CASE WHEN nick = 'John' THEN 18 ELSE age END),
        height = (CASE WHEN nick = 'Stuart' THEN 180 ELSE height END)
WHERE nick IN ('John', 'Stuart')

I really read the problem as updating only age. For this, you can use a case statement: