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 );
Results int: 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;
Returns: Count = 47200
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.