Update statement [message #19787] |
Wed, 10 April 2002 00:05 |
Cathy Zhang
Messages: 2 Registered: April 2002
|
Junior Member |
|
|
I have three tables:
S with attributes (S#,Sname,Status,City)
SP with attributes (S#,P#, Quantity)
P with attributes (P#, Pname, Color, Weight, City)
Here is what I need:
set the shipment quantity to zero for all suppliers in London.
I have 2 solutions, can you tell me which one is better?
1)
update SP
set Quantity = 0
where SP.S# = S.S#
and S.City= 'London';
2)
Update SP
Set Quantity = 0
where 'London' =
(select City
from S
where S.S# = SP.S#);
Thanks!
|
|
|
Re: Update statement [message #19799 is a reply to message #19787] |
Wed, 10 April 2002 09:12 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Option 1: will not work - is not valid SQL
Option 2: has to evaluate every row in sp
How about Option 3:
update sp
set quantity = 0
where s# in (select s# from s where city = 'London');
|
|
|
|
|