Question about update statements in sql

[russeldraper]

I'm trying to run an update statement on my database. It going to change information in one of my tables, but I need to link that table with two other tables to figure out which records need to be updated. I know that that statement should look something like this:

update s
set s.status = s.status*2
from s1 left outer join sp on s.sno = sp.sno
where sp.qty > 250;

This statement links two tables, but I need to figure out how to link this statement with a table p where p.color = 'red'. The link with for this table would be sp.pno = p.pno.

Thanks for any help.

 

[MKVAB]

Can you not add your second join to your existing statement?

update s
set s.status = s.status*2
from s1
left outer join sp
on s.sno = sp.sno
inner join p
on sp.pno = p.pno
where sp.qty > 250
and p.color = 'red';

-mk

 

[SQLBill]

update s
set s.status = s.status*2
from s
left outer join sp
on s.sno = sp.sno
join p
on sp.pno = p.pno
where sp.qty > 250
and p.color = 'red'

Note the 'fix' to the original FROM table (from s1 to just s).

Change the second JOIN to whatever type of JOIN you need it to be for your results.

-SQLBill