Quicker way to update all rows in a SQL Server table

Question

Is there a more efficient way to write this code? Or with less code?

SELECT * 
INTO #Temp 
FROM testtemplate

Declare @id INT
Declare @name VARCHAR(127)

WHILE (SELECT Count(*) FROM #Temp) > 0 
BEGIN 

    SELECT TOP 1 @id = testtemplateid FROM #Temp
    SELECT TOP 1 @name = name FROM #Temp

    UPDATE testtemplate
    SET testtemplate.vendortestcode = (SELECT test_code FROM test_code_lookup WHERE test_name = @name)
    WHERE testtemplateid = @id

     --finish processing 
    DELETE #Temp Where testtemplateid = @id
END
DROP TABLE #Temp

See http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql for a pointer on how to do this, although Joe is correct (+1) (and much faster at typing than I was!) — dash, Dec 19 '11 at 15:55

score 15 · Accepted Answer · answered Dec 19 '11 at 15:53

15

You can do this in a single UPDATE with no need to loop.

UPDATE tt
    SET vendortestcode = tcl.test_code
    FROM testtemplate tt
        INNER JOIN test_code_lookup tcl
            ON tt.name = tcl.test_name

answered Dec 19 '11 at 15:53

Joe Stefanelli

132,803
19
237
235

wow, I should have thought of that. I didn't know you could join tables that don't actually have constraints (related keys) with one another. This is way cool, and very powerful! – JustBeingHelpful Dec 20 '11 at 16:51
I know this is over 10 years old, but could anyone point a resource that explains in more detail what happens under the hood in this solution? I find it to be very elegant and would like to understand it better. – Engels Leonhardt Mar 24 '22 at 13:26
@EngelsLeonhardt [here is good article](https://www.sqlshack.com/an-overview-of-sql-server-update-join) – milos Apr 20 '22 at 14:17

score 7 · Answer 2 · answered Dec 19 '11 at 15:55

7

You could try a single update like this:

UPDATE A
SET A.vendortestcode = B.test_code
FROM testtemplate A
INNER JOIN test_code_lookup B
ON A.name = B.test_name

Also, the way you are doing it now is wrong, since you are taking a TOP 1 Id and a TOP 1 name in two separate querys, without an ORDER BY, so its not sure that you are taking the right name for your ID.

answered Dec 19 '11 at 15:55

Lamak

69,480
12
108
116

+1 for the comment on ordering! This is one of the most dangerous fallacious assumptions people have about SQL. – JNK Dec 19 '11 at 16:08
@JNK - Yeah, of all the things that could be improved of that code, I think that the lack of an `ORDER BY` on those two queries is the most urgent one. – Lamak Dec 19 '11 at 16:17

score 1 · Answer 3 · edited Dec 19 '11 at 15:58

1

You could write a function to update vendortestcode. Then your code reduces to one SQL statement:

update testtemplate set vendortestcode = dbo.get_test_code_from_name(name)

edited Dec 19 '11 at 15:58

DaveShaw

52,123
16
112
141

answered Dec 19 '11 at 15:58

jeff.eynon

1,296
3
11
29

Quicker way to update all rows in a SQL Server table

3 Answers3