Preferred method of updating and setting SQL

Question

I'm learning SQL at the moment, and have found some different methods of updating and setting a table.

For this example, I am wondering what is the "most correct way" of doing this?

    UPDATE student
    SET test1 = 7, test2 = 9
    where stuid = 999

or

    UPDATE student
    SET test1 = 7
    where stuid = 999
    UPDATE student
    SET test2 = 9
    where stuid = 999

Thanks.

I would say method1 without any doubt ... why to use two queries if we can use 1... — alwaysLearn, Apr 01 '13 at 09:31

score 1 · Answer 1 · answered Apr 01 '13 at 09:31

1

Running one update statement is always better than two. I choose the first one. It will only run on the database once and updates two columns.

answered Apr 01 '13 at 09:31

John Woo

score 1 · Answer 2 · answered Apr 01 '13 at 09:32

1

I will go with first statement because its just 1 call to database server.

answered Apr 01 '13 at 09:32

score 1 · Answer 3 · answered Apr 01 '13 at 09:34

1

I suggest the first, don't repeat yourself, and always write neat codes.

answered Apr 01 '13 at 09:34

Roger Liu

score 1 · Answer 4 · answered Apr 01 '13 at 09:36

1

Statement 1 is without any doubt better than statement 2 ... Why to load the db server twice than required ...

answered Apr 01 '13 at 09:36

alwaysLearn

score 1 · Accepted Answer · answered Apr 01 '13 at 09:42

1

Firstly always Google yourself before posting any Questions.

The answer (as said by others) is off-course the Method 1. As it will not reload the database Twice.

Also if you are Beginner try to go learn from W3Schools

answered Apr 01 '13 at 09:42

Prahalad Gaggar

i actually did google and that's what prompted me to ask the question :) http://stackoverflow.com/questions/6446250/sql-statement-with-multiple-sets-and-wheres Here, i can see that almost every single answer states to use MULTIPLE statements, where only one answer uses 'method 1' in my OP, which everyone here is stating is correct. – Anteara Apr 01 '13 at 10:03
It that Particular **Question**, as the heading Suggests the **OP** wants to update multiple column with **multiple Where condition**. But in your case you are updating on same **where Condition**. – Prahalad Gaggar Apr 01 '13 at 10:16
Ah! I overlooked that part! Thanks! – Anteara Apr 01 '13 at 11:39

5 Answers5