8 Replies Latest reply: Oct 12, 2012 1:42 AM by Marwim RSS

    Updating wrong number of records

    Chanchal Wankhade
      Hi all,

      my one table is having only 5 records
      SQL> select * From serial_no;
      
      SERIAL_NO            USER_NAME
      -------------------- -----------------
      INA73709H3           Moni rora
      INA81904H1           Vari Singh
      INA8370841           Meg poor
      CND822XDWM           Biren Singh
      CNCKL02255           Meg poor
      My other table is having 3000 records. Now i need to update the user_id column of my second table with the user_name column of serial_no table which is having serial number unique column and i am executing following command
      UPDATE BKP A SET USER_ID=(SELECT USER_NAME FROM SERIAL_NO B WHERE A.SERIAL_NO=B.SERIAL_NO)
      In this case it should update only 5 records which are having same serial number but this command updating 2197 records.

      Can anybody tell me why this is so.
        • 1. Re: Updating wrong number of records
          908002
          why you feel it should update only 5 rows.

          It will update the number of records in bkp table where it has matched serial number with serial_no table.
          • 2. Re: Updating wrong number of records
            jeneesh
            UPDATE BKP A SET USER_ID=(SELECT USER_NAME FROM SERIAL_NO B WHERE A.SERIAL_NO=B.SERIAL_NO)
            where exists 
             (SELECT null FROM SERIAL_NO B WHERE A.SERIAL_NO=B.SERIAL_NO) 
            Or use NVL or MERGE, to avoid two scans on table..
            • 3. Re: Updating wrong number of records
              Chanchal Wankhade
              Hi,

              There are only 5 matching records I found.
              • 4. Re: Updating wrong number of records
                jeneesh
                Chanchal Wankhade wrote:
                Hi,

                There are only 5 matching records I found.
                It is updating all the records, because there is no where condition for the UPDATE statement... Non matching records will get updated with null....

                You can add where condition using exists....

                Or use MERGE without WHEN NOT MATCHED part- preferably...

                Edited by: jeneesh on Oct 12, 2012 11:59 AM
                • 5. Re: Updating wrong number of records
                  908002
                  In remaining cases it is updating to null, as it not foind matching records. add condition to update statement.
                  • 6. Re: Updating wrong number of records
                    Marwim
                    Hello,
                    DROP TABLE bkp;
                    DROP TABLE serial_no;
                    CREATE TABLE serial_no(
                       serial_no VARCHAR2(10)
                      ,user_name VARCHAR2(20)
                      );
                    
                    INSERT INTO serial_no(serial_no,user_name)
                    VALUES('INA73709H3','Moni rora');
                    INSERT INTO serial_no(serial_no,user_name)
                    VALUES('INA81904H1','Vari Singh');
                    INSERT INTO serial_no(serial_no,user_name)
                    VALUES('INA8370841','Meg poor');
                    INSERT INTO serial_no(serial_no,user_name)
                    VALUES('CND822XDWM','Biren Singh');
                    INSERT INTO serial_no(serial_no,user_name)
                    VALUES('CNCKL02255','Meg poor');
                    
                    CREATE TABLE bkp(
                       serial_no VARCHAR2(10)
                      ,user_id VARCHAR2(20)
                      );
                    INSERT INTO bkp(serial_no)
                    VALUES('INA73709H3');
                    INSERT INTO bkp(serial_no)
                    VALUES('INA81904H1');
                    INSERT INTO bkp(serial_no)
                    VALUES('INA8370841');
                    INSERT INTO bkp(serial_no)
                    VALUES('CND822XDWM');
                    INSERT INTO bkp(serial_no)
                    VALUES('CNCKL02255');
                    
                    INSERT INTO bkp(serial_no)
                    VALUES('rsdtgfrvsc');
                    INSERT INTO bkp(serial_no)
                    VALUES('bwtvvgtstv');
                    INSERT INTO bkp(serial_no)
                    VALUES('vrgsercrgc');
                    INSERT INTO bkp(serial_no)
                    VALUES('rstvhthimm');
                    INSERT INTO bkp(serial_no)
                    VALUES('mugnkuknnu');
                    COMMIT;
                    UPDATE  bkp a
                    SET     user_id = (
                                SELECT user_name
                                FROM serial_no b
                                WHERE a.serial_no = b.serial_no
                                );
                    SELECT  *
                    FROM    bkp;
                    
                    
                    SERIAL_NO  USER_ID            
                    ---------- --------------------
                    INA73709H3 Moni rora            
                    INA81904H1 Vari Singh           
                    INA8370841 Meg poor             
                    CND822XDWM Biren Singh          
                    CNCKL02255 Meg poor             
                    rsdtgfrvsc                      
                    bwtvvgtstv                      
                    vrgsercrgc                      
                    rstvhthimm                      
                    mugnkuknnu              
                    As you can see, only the matching rows will be updated. Oracle will count the unchanged rows, if you don't add a where clause that excludes the unchanged rows.

                    Regards
                    Marcus

                    Edited by: Marwim on 12.10.2012 08:39
                    Merge might be a better solution
                    MERGE INTO bkp a
                    USING ( SELECT  user_name
                                   ,serial_no
                            FROM    serial_no b
                            )b
                      ON (a.serial_no = b.serial_no)
                    WHEN MATCHED THEN 
                         UPDATE
                        SET user_id = b.user_name;
                    • 7. Re: Updating wrong number of records
                      jeneesh
                      Marwim wrote:
                      As you can see, only the matching rows will be updated. Oracle will count the unchanged rows, if you don't add a where clause that excludes the unchanged rows.
                      It is not correct.. The unmatched rows will be updated as NULL
                      SQL> update bkp set user_id= 'XXXXXX' where serial_no= 'rsdtgfrvsc';
                      
                      1 row updated.
                      
                      SQL> select * from bkp;
                      
                      SERIAL_NO  USER_ID
                      ---------- --------------------
                      INA73709H3 Moni rora
                      INA81904H1 Vari Singh
                      INA8370841 Meg poor
                      CND822XDWM Biren Singh
                      CNCKL02255 Meg poor
                      rsdtgfrvsc XXXXXX
                      bwtvvgtstv
                      vrgsercrgc
                      rstvhthimm
                      mugnkuknnu
                      
                      10 rows selected.
                      
                      SQL> UPDATE  bkp a
                        2  SET     user_id = (
                        3              SELECT user_name
                        4              FROM serial_no b
                        5              WHERE a.serial_no = b.serial_no
                        6              );
                      
                      10 rows updated.
                      
                      SQL>  select * from bkp;
                      
                      SERIAL_NO  USER_ID
                      ---------- --------------------
                      INA73709H3 Moni rora
                      INA81904H1 Vari Singh
                      INA8370841 Meg poor
                      CND822XDWM Biren Singh
                      CNCKL02255 Meg poor
                      rsdtgfrvsc
                      bwtvvgtstv
                      vrgsercrgc
                      rstvhthimm
                      mugnkuknnu
                      
                      10 rows selected.
                      • 8. Re: Updating wrong number of records
                        Marwim
                        It is not correct.. The unmatched rows will be updated as NULL
                        correct