April 14, 2009 at 8:28 am
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
April 14, 2009 at 8:40 am
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
April 14, 2009 at 9:07 am
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
April 14, 2009 at 10:33 am
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.
April 14, 2009 at 10:37 am
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.
April 14, 2009 at 10:41 am
rgillings (4/14/2009)
From what I understand, and please correct me if I am wrong, UPDATE FROM is not ANSI standardSo with the MERGE statement introduced in SQL 2008, it is preferred to use that command instead.
I agree with you..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply