Geek asks and answers

Why do different values ​​affect my updated sql query?

A very simple question: I have an update that I would like to do when I do this update, then it will affect 2000+ rows, but when I just make a select request in the subquery, then I will get 1726 rows. I know that something is wrong in my update request, can someone 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
                   )

Thus, only 1726 rows are returned over the executed subquery, but when I execute the entire update request, it processes more than 2000 rows, I want to make only 1726?

+3
source share
3 answers

Since you are updating rows, this should not be updated.

ship_plu.pluc_dtmay meet the conditions, ship_plu.ship_num not .

This is the wrong way to update.

:

update ship_plu sp 
       JOIN ship s
            ON sp.ship_num=s.ship_num 
   set pluc_dt='1-Jan-1999' 
 where pluc_dt between '16-Feb-2014' and '20-Feb-2014'
       and s.rcv_dt is null;

( , ship_num - ):

update ship_plu 
   set pluc_dt='1-Jan-1999' 
 where ship_num in (
                   select sp.ship_num 
                     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
                   );

, , .

+1

. , . :

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
                   );

. ( join), .

+2

, , :

    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

ship_plu table . ( s.rcv_dt null, ), ship_plu .

, , pluc_dt, s.rcv_dt null.

. :

    update ship_plu 
    set pluc_dt='1-Jan-1999' 
    where ID in (
        select sp.ID 
        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
        )

, !

0

More articles:


All Articles