SQL Server Performance Forum – Threads Archive

how to combine two UPDATES…

Hi all,
my question is just if i have two update commands then how can i join them…here is my update:
UPDATE LINKS
SET PRICE = 999
FROM LINKS L JOIN PRODUCTS P ON (L.PRODUCT=P.ID) JOIN COMPANIES C ON (L.COMPANY=C.ID)
WHERE P.MODULES=3 AND C.EMPLOYEES=30 UPDATE LINKS
SET PRICE = 299
FROM LINKS L JOIN PRODUCTS P ON (L.PRODUCT=P.ID) JOIN COMPANIES C ON (L.COMPANY=C.ID)
WHERE P.MODULES=4 AND C.EMPLOYEES=10 How can i join these two into ONE COMMAND????
Thanks in advance,
Regards,

You need to Run them seperately or (Take backup of those tables) UPDATE LINKS
SET PRICE = Case when P.MODULES=3 AND C.EMPLOYEES=30 then 999
when P.MODULES=3 AND C.EMPLOYEES=30 then 299 end
FROM LINKS L JOIN PRODUCTS P ON (L.PRODUCT=P.ID) JOIN COMPANIES C ON (L.COMPANY=C.ID)
WHERE (P.MODULES=3 AND C.EMPLOYEES=30) or (P.MODULES=4 AND C.EMPLOYEES=10) Madhivanan Failing to plan is Planning to fail

Thanks for your reply..but unfortunately it doesnt work….<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> it says : The multi-part identifier "L.PRICE" could not be bound. and i cant execute them separately..thats the main point…i want to execute those two queries as ONE QUERY…any other suggestions???? regards.

Try this UPDATE L
SET L.PRICE = Case when P.MODULES=3 AND C.EMPLOYEES=30 then 999
when P.MODULES=3 AND C.EMPLOYEES=30 then 299 end
FROM LINKS L JOIN PRODUCTS P ON (L.PRODUCT=P.ID) JOIN COMPANIES C ON (L.COMPANY=C.ID)
WHERE (P.MODULES=3 AND C.EMPLOYEES=30) or (P.MODULES=4 AND C.EMPLOYEES=10) Madhivanan Failing to plan is Planning to fail

Yeah thats exactly wat i was writtin before….the query that u sent before was givin other errors so,i wrote:
UPDATE LINKS
SET L.PRICE = Case when P.MODULES=3 AND C.EMPLOYEES=30 then 999
when P.MODULES=3 AND C.EMPLOYEES=30 then 299 end
FROM LINKS L JOIN PRODUCTS P ON (L.PRODUCT=P.ID) JOIN COMPANIES C ON (L.COMPANY=C.ID)
WHERE (P.MODULES=3 AND C.EMPLOYEES=30) or (P.MODULES=4 AND C.EMPLOYEES=10)
and then i got the error that i mentioned in the last post…i.e :
The multi-part identifier "L.PRICE" could not be bound.[:0]

if i m just writtin UPDATE L then it says:
Cannot insert the value NULL into column ‘PRICE’, table ‘EXTRAS.dbo.LINKS’; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Also Try this UPDATE L
SET L.PRICE = Case when P.MODULES=3 AND C.EMPLOYEES=30 then 999
when P.MODULES=3 AND C.EMPLOYEES=30 then 299 else L.PRICE end
FROM LINKS L JOIN PRODUCTS P ON (L.PRODUCT=P.ID) JOIN COMPANIES C ON (L.COMPANY=C.ID)
WHERE (P.MODULES=3 AND C.EMPLOYEES=30) or (P.MODULES=4 AND C.EMPLOYEES=10)
Madhivanan Failing to plan is Planning to fail

GREATTTTTTTTTTTTTT Madhivanan!!!!!thanks a lotttttttt….[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]..it worked…

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by cutiepie GREATTTTTTTTTTTTTT Madhivanan!!!!!thanks a lotttttttt….[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]..it worked… <hr height="1" noshade id="quote"></blockquote id="quote"> Well. Anyway you need to understand the logic used [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Well, he needs to understand that he has to use the alias which he has set in the FROM part …

>>Well, he needs to understand that he has to use the alias which he has set in the FROM part … I hope he noticed it Madhivanan Failing to plan is Planning to fail

Yeah i noticed that…and can u tell me y is it like that????n in wat all cases we have to use ALIASES instead of table name??
Regards,
Shuchi.

You don’t have to use an alias, but if you do then you must use the alias in all parts of your query – including the UPDATE part.

]]>