Using Join with Update

Question

Using Join with Update

Matthew24

SSCommitted

Points: 1736
More actions
April 14, 2009 at 8:28 am

#202928

Hi,
I've worked out how to use Join with Select Statements, but I need to use it with an Update statement.
I've tried:
update jobs
set comm = 0
join customer on jobs.cus_id=customer.cus_id
where customer.rate_group = '1'
and weekno = 200902
but no joy, what am I missing ??
Thanks,
Matt

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

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

rgillings SSCrazy Points: 2114 More actions · Answer 1

m.dunster (4/14/2009)
Hi,
I've worked out how to use Join with Select Statements, but I need to use it with an Update statement.
I've tried:
update jobs
set comm = 0
join customer on jobs.cus_id=customer.cus_id
where customer.rate_group = '1'
and weekno = 200902
but no joy, what am I missing ??
Thanks,
Matt

try

update j

set comm = 0

from customer c, jobs j

where c.cus_id = j.cus_id

and c.rate_group = '1'

and weekno = 200902

Matthew24 SSCommitted Points: 1736 More actions · Answer 2

Hi

Thanks for the reply.

I managed to get the following working:

update jobs

set comm = 0

from jobs join customer on jobs.cus_id=customer.cus_id

where customer.rate_group = '1'

and weekno = 200902

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 3

This is what i like about the SQL Server, Updates using is Join is really kool. In Oracle there is no way you can perform Joins in Updates unless you use Sub Queries.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

rgillings SSCrazy Points: 2114 More actions · Answer 4

From what I understand, and please correct me if I am wrong, UPDATE FROM is not ANSI standard

So with the MERGE statement introduced in SQL 2008, it is preferred to use that command instead.

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

rgillings (4/14/2009)
From what I understand, and please correct me if I am wrong, UPDATE FROM is not ANSI standard
So with the MERGE statement introduced in SQL 2008, it is preferred to use that command instead.

I agree with you..

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers