SQL 使用新值更新临时表中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13035651/
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
Updating a column in a temp table with new values
提问by Mellonjollie
I have the following temp table structure:
我有以下临时表结构：
CREATE TABLE #TempTable (
ID INT,
CId TINYINT,
TagId INT,
Beginning_X DECIMAL(18,5),
Beginning_Y DECIMAL(18,5),
Ending_X DECIMAL(18,5),
Ending_Y DECIMAL(18,5))
INSERT INTO #TempTable (ID, CId, TagId, Beginning_X, Beginning_Y)
SELECT ID, CId,TagId, X, Y
FROM Table_1
WHERE IsRepeat = 1
INSERT INTO #TempTable(Ending_X, Ending_Y)
SELECT X,Y
FROM Table_1 t
WHERE t.ID IN (SELECT ID+1
FROM Table_1
where IsRepeat = 1))
The second insert removes all the values from the first insert statement and I can't figure out why. I want to append the the results from the second insert to the first insert and have one solid table.
第二个插入删除了第一个插入语句中的所有值，我不知道为什么。我想将第二个插入的结果附加到第一个插入并有一个实体表。
EDIT: I think I found the solution:
编辑：我想我找到了解决方案：
UPDATE t
SET t.Ending_X = p.X, t.Ending_Y = p.Y
FROM #TempTable t, Table_1 p
WHERE p.ID IN (SELECT ID+1 FROM Table_1 where IsRepeat = 1)
AND p.ID-1 = t.ID
回答by rs.
I think you need this
我想你需要这个
UPDATE t
SET Ending_X = X, Ending_Y = Y
FROM #TempTable t
WHERE t.ID IN (SELECT ID+1 FROM Table_1 where IsRepeat = 1))
回答by tgolisch
How about unioning the two queries
如何联合两个查询
INSERT INTO #TempTable (ID, CId, TagId, Beginning_X, Beginning_Y)
SELECT ID, CId,TagId, X, Y
from Table_1
WHERE IsRepeat = 1
UNION
SELECT Convert(Null, int) AS ID, Convert(Null, tinyint) AS CId,
Convert(Null, int) AS TagId, X,Y
FROM Table_1 t
WHERE t.ID IN (SELECT ID+1 FROM Table_1 where IsRepeat = 1))
