Help with UPDATE

Question

Post reply

Help with UPDATE

krushkoder

Ten Centuries

Points: 1149
More actions
March 15, 2011 at 12:08 pm

#147604

I need to convert the SELECT statement below, to an UPDATE statement to populate the 3 values in the SELECT (A_ID, A.passID, C.clientID) INTO my #Temp table.

Sorry, brain still stuck in the bog... waiting on the towtruck! 😀

TIA,

KK

SELECT A.ID as A_ID, A.passID, C.clientID FROM Table_A A LEFT JOIN #Temp T ON T.passNum = A.passNum JOIN Table_C C ON A.passID = C.passID WHERE (A.prodID = T.prodID)

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

I think it's going to look something like this: I'd ratehr see some sample data so we know it's working, but here is my best guess:



UPDATE A

SET  A.ID       = T.ID,

     A.passID   = T.passID,

     A.clientID = C.clientID

FROM Table_A A

        LEFT JOIN #Temp T ON T.passNum = A.passNum

        JOIN Table_C C ON A.passID = C.passID

WHERE (A.prodID = T.prodID)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

If I'm correct you intended to opdate the temp table ?

You should modify the code Lowell provided to update the correct target !

(his query updates the data in the A table)

In that case, convert the left join to an inner join.



UPDATE T

SET       clientID = A.clientID

FROM Table_A A

        INNER JOIN #Temp T ON T.passNum = A.passNum

        INNER JOIN Table_C C ON A.passID = C.passID

WHERE (A.prodID = T.prodID)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

krushkoder Ten Centuries Points: 1149 More actions · Answer 3

Lowell,

Thanks for the quick reply. That is close to what I had, except I need to be able to UPDATE #Temp T with the values from A and C.

Here's what I have so far:



UPDATE #Temp

SET

     A_ID      = A.ID,

     passID   = A.passID,

     clientID = C.clientID

FROM Table_A A

     JOIN Table_C C ON A.passID = C.passID

... but alas, without the other JOIN/WHERE clauses, I get about 2 million rows (Cartesian, of course!) when I'm only expecting 1500... Not sure if I need to JOIN the #Temp table or do a WHERE?

Thanks,

KK

P.S. I guess I hit the TAB and SPACE keys which navigated me right to the "Post Topic" buttons. This is the corrected version... KK

krushkoder Ten Centuries Points: 1149 More actions · Answer 4

krushkoder

Ten Centuries

Points: 1149

March 15, 2011 at 12:33 pm

#1299054

ALZDBA,

Thanks for that. I hadn't tried a different JOIN type.

Many Thanks!

KK

krushkoder Ten Centuries Points: 1149 More actions · Answer 5

Don't you just love "expanding scope"?

I got the query working (Or rather you gurus did!) and now I find out that it needs to be a "conditional" update...

This works GREAT!



UPDATE T

SET

	A_ID	 = A.ID

	passID	 = A.PassID

	clientID = C.clientID

FROM Table_A A

        INNER JOIN #Temp T ON T.passNum = A.passNum

        INNER JOIN Table_C C ON A.passID = C.passID

WHERE (A.prodID = T.prodID)

However... Now I need to make it update based on the value of T.passTypeID from the #Temp table. The explanation I got goes something like this...

If the value of passTypeID = 0 I can do the above with satisfactory results. If however, the value of passTypeID > 0, I need to JOIN or filter WHERE A.prodID = T.prodID AND A.passTypeID = T.passTypeID.

I'm not sure if I should I use a CASE or an IF and where to put it? Outside of the UPDATE? Or is there a way to do a "conditional join"?

Again, any and all guidance is greatly appreciated.

TIA,

KK

krushkoder Ten Centuries Points: 1149 More actions · Answer 6

krushkoder

Ten Centuries

Points: 1149

March 22, 2011 at 3:16 pm

#1302199

I still have not been able to make this work correctly 100% of the time...

Anybody have any ideas?