SQL 如何更新#temptable
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15873234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to UPDATE #temptable
提问by FireShock
CREATE TABLE #TempProducts (
Id uniqueidentifier,
ManufacturerId uniqueidentifier,
Number varchar(50),
PresentId uniqueidentifier null)
How to UPDATE PresentId field? I have different errors in this:
如何更新 PresentId 字段？我在这方面有不同的错误：
1) There is already an object named '#TempProducts' in the database.
1) 数据库中已经有一个名为“#TempProducts”的对象。
UPDATE #TempProducts
SET #TempProducts.PresentId = p.Id
FROM #TempProducts JOIN Products p ON (#TempProducts.ManufacturerId = p.ManufacturerId AND #TempProducts.Number = p.Number)
WHERE #TempProducts.Id <> p.Id
2) The multi-part identifier "t.PresentId" could not be bound.
2) 无法绑定多部分标识符“t.PresentId”。
UPDATE #TempProducts
SET t.PresentId = p.Id
FROM #TempProducts t JOIN Products p ON (t.ManufacturerId = p.ManufacturerId AND t.Number = p.Number)
WHERE t.Id <> p.Id
回答by TechDo
For the Second Error please try:
对于第二个错误，请尝试：
UPDATE t
SET t.PresentId = p.Id
FROM #TempProducts t JOIN Products p ON (t.ManufacturerId = p.ManufacturerId AND t.Number = p.Number)
WHERE t.Id <> p.Id
回答by Luv
For problem 1:(Before the CREATE TABLEsyntax )
对于问题 1:(在CREATE TABLE语法之前)
if object_id(tempdb..#TempProducts) is not null
begin
drop table #TempProducts
end
And for problem 2 i think @techdois correct.
对于问题 2，我认为@techdo是正确的。
UPDATE t
SET t.PresentId = p.Id
FROM #TempProducts t JOIN Products p
ON (t.ManufacturerId = p.ManufacturerId AND t.Number = p.Number)
WHERE t.Id <> p.Id
回答by Nenad Zivkovic
UPDATE t
SET t.PresentId = p.Id
FROM #TempProducts t JOIN Products p ON (t.ManufacturerId = p.ManufacturerId AND t.Number = p.Number)
WHERE t.Id <> p.Id
This would be fix for a second error (UPDATE t instead of UPDATE #TempProducts). However, there is no way first query could produce mentioned error.
这将修复第二个错误（UPDATE t 而不是 UPDATE #TempProducts）。但是，第一个查询不可能产生提到的错误。
I am guessing you are executing your CREATE TABLE query again which is producing error because temp table already exists.
我猜你正在再次执行你的 CREATE TABLE 查询，这会产生错误，因为临时表已经存在。
You should use a check like this when creating temp tables:
创建临时表时，您应该使用这样的检查：
IF (OBJECT_ID('tempdb..#TempProducts') IS NOT NULL) DROP TABLE #TempProducts
CREATE TABLE #TempProducts (
Id uniqueidentifier,
ManufacturerId uniqueidentifier,
Number varchar(50),
PresentId uniqueidentifier null)
