Quicker 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
您可以在单个UPDATE中执行此操作,而无需循环。
UPDATE tt
SET vendortestcode = tcl.test_code
FROM testtemplate tt
INNER JOIN test_code_lookup tcl
ON tt.name = tcl.test_name
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.
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)
链接地址: http://www.djcxy.com/p/94670.html
上一篇: sql查询更新INNER JOINED表
下一篇: 快速更新SQL Server表中的所有行