Updating a column in a temp table with new values
25,506
Solution 1
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))
Solution 2
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))
Author by
Mellonjollie
College student learning SQL Server and VB. I currently work as an intern at a small tech firm near campus.
Updated on July 28, 2022Comments
-
Mellonjollie almost 2 years
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