Why are different values being affected in my update sql query?

Question

A very basic question, I have an update I would like to do when I do the update then it is affecting 2000+ rows but when I just do the select query in the subquery then I get 1726 rows. I know there is something wrong in my update statement, can someone please help?

update ship_plu 
   set pluc_dt='1-Jan-1999' 
 where pluc_dt in (
                   select sp.pluc_dt 
                     from ship_plu sp,ship s 
                    where sp.pluc_dt between '16-Feb-2014' and '20-Feb-2014'
                          and sp.ship_num=s.ship_num 
                          and s.rcv_dt is null
                   )

So above the subquery executed only brings back 1726 rows, but when I execute the entire update query then it effects over 2000 rows, I want to do just 1726?

Answer 1

You want a correlated subquery. But you have the inner subquery referring to the outside table. Try this:

update ship_plu sp
   set pluc_dt='1-Jan-1999' 
 where pluc_dt in (
                   select sp.pluc_dt 
                     from ship s 
                    where sp.pluc_dt between '16-Feb-2014' and '20-Feb-2014'
                          and sp.ship_num=s.ship_num 
                          and s.rcv_dt is null
                   );

This form of the query will work in any database. Depending on the actual database you are using, there is other syntax (using join) that you could use.