Update with Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update with Joins

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-12-03 : 10:20:13

Can someone tell me what is wrong ? Im trying to perform an update on a table based only if the corresponding rows exist in a temp table.

UPDATE TEST
SET
B = CASE
WHEN (CASE WHEN ISNULL(S,0) <> 0
THEN G * H
END) = 0
THEN NULL
ELSE (G + K
END)
END
FROM TEST T
INNER JOIN #L TP
ON
T.A1 =TP.A1 AND
T.A2 =TP.A2 AND
T.A3 = TP.A3

I get error Ambiguous column name 'G'
Column G exist in both TEST & #L

Meanwhile Column H,K & S only exist in TEST table

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 10:22:27

You need to specify the table the columns come from

TP.G or T.G
do that for all of the columns even if they are not ambiguous to make things clear.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-12-03 : 11:22:34

Thanks folks.It worked like a charm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources