Update with Joins

Question

Post reply

Update with Joins

LOOKUP_BI-756009

SSCertifiable

Points: 7286
More actions
December 3, 2010 at 8:22 am

#232140

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

LOOKUP_BI-756009 (12/3/2010)
Column G exist in both TEST & #L

Exactly - so you need to specify which G you want. Qualify your column name so that it is "TP.G" or "T.G".

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Fix the obvious syntax error, then use table aliases throughout the CASE construct.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 4

LOOKUP_BI-756009

SSCertifiable

Points: 7286

December 3, 2010 at 9:22 am

#1258492

Thanks folks.It worked like a charm