Dilan Perera Dilan Perera - 2 months ago 6
SQL Question

Oracle Update Statement clarification required

I am updating a table based on values from another table using the Serial Number field as the common field to look up values. However from below 2 queries i get two different results. Can some one explain why the two outputs are different? Should not the update statement update 47200 records?

UPDATE TBL_SERIAL_NUMBER_MASTER A
SET (A.name) = (SELECT B.name FROM TBL_DEVICE_LOCALITY B WHERE A.SERIAL_NUMBER = B.SERIAL_NUMBER AND ROWNUM <=1 )
WHERE EXISTS
( SELECT 1 FROM TBL_DEVICE_LOCALITY WHERE SERIAL_NUMBER = A.SERIAL_NUMBER AND TBL_ODIN_DEVICE_LOCALITY.HOST_NAME IS NOT NULL );





35,311 rows updated.




select count(*)
from TBL_SERIAL_NUMBER_MASTER A, TBL_DEVICE_LOCALITY B
WHERE A.SERIAL_NUMBER = B.SERIAL_NUMBER AND B.HOST_NAME IS NOT NULL;





Count = 47200



Answer

First, you should learn to use proper explicit JOIN syntax. So, the second query should be:

select count(*)
from TBL_SERIAL_NUMBER_MASTER A JOIN
     TBL_DEVICE_LOCALITY B
     ON A.SERIAL_NUMBER = B.SERIAL_NUMBER 
where B.HOST_NAME IS NOT NULL;

You are getting the results you see because the two queries are not the same. Your results suggests that SERIAL_NUMBER is not unique in the B table, so the JOIN is multiplying rows. On the other hand, the UPDATE is updating rows in A, regardless of the number of matches in B.

To compare like to like, use:

select count(*)
from TBL_SERIAL_NUMBER_MASTER A JOIN
     TBL_DEVICE_LOCALITY B
     ON A.SERIAL_NUMBER = B.SERIAL_NUMBER 
where exists (select 1 
              from TBL_DEVICE_LOCALITY B
              where B.SERIAL_NUMBER = A.SERIAL_NUMBER AND
                    B.HOST_NAME IS NOT NULL
             );

Or, if you have a unique/primary key column in A, then you can use:

select count(distinct A.??)
from TBL_SERIAL_NUMBER_MASTER A JOIN
     TBL_DEVICE_LOCALITY B
     ON A.SERIAL_NUMBER = B.SERIAL_NUMBER 
where B.HOST_NAME IS NOT NULL;

Where ?? is the unique/primary key column.