May 31, 2006 at 10:01 am
im trying to update many columns in the one update statement, but i keep getting an error.
here is the update statement:
update
reportTable
set
programName = description from program p inner join reportTable r on p.programID = r.programId where p.programId = r.programID,
period
= currentPeriod from program p inner join reportTable r on p.programID = r.programId where p.programId = r.programID
Here is the error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Anyone know what is wrong with my statement?
May 31, 2006 at 10:05 am
You can only have one JOIN / WHERE combination in an update statement. Thus, this should be:
UPDATE reportTable
SET programName = description,
period = currentPeriod
FROM ReportTable r
INNER JOIN program p
ON p.programID = r.programId
May 31, 2006 at 10:17 am
thank you. thats something i didnt know and would have bothered me for ages.
May 31, 2006 at 2:40 pm
You mean one SET clause, one FROM clause and one WHERE clause. You can have as as many JOINs as any other query.
BTW, on a slightly pedantic note, you should add the table modifier to the columns on the right hand (assignor) side of the assignment (=) operators, since they could be ambiguous.
Since there is always just one target table, the columns on the LHS (assignee) side can't be ambiguous, and in fact a table modifier is not allowed.
Also an alias should be used instead of the name of the target table in the UPDATE clause since the unaliased name is ambiguous between multiple instances in the FROM clause:
UPDATE r
SET programName = p.description,
period = p.currentPeriod
FROM ReportTable r
INNER JOIN program p
ON p.programID = r.programId
--[+ more joins...+ where clause...]
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 31, 2006 at 4:34 pm
A table modifier is allowed in the assignee side of an update. The following works perfectly well.
DECLARE @table1 TABLE (id int, data varchar(5) null)
INSERT @table1 values(1, null)
INSERT @table1 values(2, null)
DECLARE @table2 TABLE (id int, srcdata varchar(5))
INSERT @table2 values(1, 'abcde')
INSERT @table2 values(2, 'zyxwv')
UPDATE t1
SET t1.data = t2.srcdata
FROM @table1 t1
INNER JOIN @table2 t2
ON t1.ID = t2.ID
SELECT * FROM @table1
May 31, 2006 at 5:17 pm
Funny, I always thought it was disallowed. Maybe it was in v6.5 or 7 and I never realised they had changed it? The matter's not really documented, so I don't know. Thanks for the correction though.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply