March 15, 2011 at 12:08 pm
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)
March 15, 2011 at 12:17 pm
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
March 15, 2011 at 12:20 pm
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
March 15, 2011 at 12:22 pm
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
March 15, 2011 at 12:33 pm
ALZDBA,
Thanks for that. I hadn't tried a different JOIN type.
Many Thanks!
KK
March 15, 2011 at 1:45 pm
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
March 22, 2011 at 3:16 pm
I still have not been able to make this work correctly 100% of the time...
Anybody have any ideas?
TIA,
KK
March 23, 2011 at 12:51 am
I'm sorry, this thread lost my attention ... or how should I put it 😉
Did you try:
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
and ( T.passTypeID = 0
or ( T.passTypeID > 0
and A.passTypeID = T.passTypeID
)
)
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply