Geek Answers Handbook

A quick way to update all rows in a SQL Server table

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
+5
source share
3 answers

You can do this in one UPDATE, without the need to quote.

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

You can try one update:

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

In addition, the way that you are doing this right now is incorrect , because you accept the TOP-1 ID and the name TOP 1 in two separate requests without ORDER BY, so it’s not sure that you accept the correct name for your identifier.

+5
source

vendortestcode. SQL:

update testtemplate set vendortestcode = dbo.get_test_code_from_name(name)
+1

More articles:


All Articles