-1

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

user3304007
  • 446
  • 1
  • 7
  • 17

1 Answers1

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:

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I thought of this at first, but it seems he wants to change the age for john and height for stuart – e4c5 Sep 12 '16 at 01:09
  • Correct me if I'm wrong, but that only updates age ? Also no its not age, I just simplified for the question, actually they're normal text values. – user3304007 Sep 12 '16 at 01:10
  • 1
    @user3304007 . . . You are right. I really read the original question and thought both queries were updating age, but that seems to have been a misreading on my part. – Gordon Linoff Sep 12 '16 at 01:22
  • Gotcha. Can I ask you which one should I use for better performance ? Lets say the database is huge (too many users), 2 update queries, or 1 query with case statement ? – user3304007 Sep 12 '16 at 01:29
  • The performance should be similar, if you do the two updates in a single transaction. If they are in multiple transactions, then the single `update` should be faster. – Gordon Linoff Sep 12 '16 at 01:50