October 6, 2014 at 10:50 pm
Hello,
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table
I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
I would like to ask for help fixing the syntax I currently have, if possible.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
Thank you.
October 6, 2014 at 10:59 pm
I think this is the right way?:
UPDATE a
SET [Talks] = b.found_talks_type
FROM [dbo].[mp_sessions_teamcouples] a
INNER JOIN @Find_Missing_Talks b
ON a.sessionid = b.sessionid
AND a.coupleid = b.coupleid
October 7, 2014 at 1:28 am
You are more or less there, here is a run-able self explanetory example
😎
USE tempdb;
GO
CREATE TABLE dbo.TBL_TO_UPDATE
(
TTU_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,TTU_VALUE INT NOT NULL DEFAULT(0)
);
DECLARE @UPDATE_VALUES TABLE
(
UPD_ID INT PRIMARY KEY CLUSTERED NOT NULL
,UPD_VALUE AS (UPD_ID * 3)
);
INSERT INTO dbo.TBL_TO_UPDATE(TTU_VALUE)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT
TTU.TTU_ID
,TTU.TTU_VALUE
FROM dbo.TBL_TO_UPDATE TTU;
INSERT INTO @UPDATE_VALUES(UPD_ID)
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT
UV.UPD_ID
,UV.UPD_VALUE
FROM @UPDATE_VALUES UV;
UPDATE TTU
SET TTU.TTU_VALUE = UV.UPD_VALUE
FROM @UPDATE_VALUES UV
INNER JOIN dbo.TBL_TO_UPDATE TTU
ON UV.UPD_ID = TTU.TTU_ID;
SELECT
TTU.TTU_ID
,TTU.TTU_VALUE
FROM dbo.TBL_TO_UPDATE TTU;
DROP TABLE dbo.TBL_TO_UPDATE;
Results
Initial table values
TTU_ID TTU_VALUE
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
Update values
UPD_ID UPD_VALUE
----------- -----------
1 3
2 6
3 9
4 12
5 15
6 18
7 21
8 24
9 27
10 30
Updated values
TTU_ID TTU_VALUE
----------- -----------
1 3
2 6
3 9
4 12
5 15
6 18
7 21
8 24
9 27
10 30
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply