I am using Oracle and I am trying to update a table(A) with data from another table(B). Not every field in B has a value so I have a number of NULL entries. When I run the update it says 6000 rows updated. Now there are 6000 rows in table B, however for this query only 14 have data in. When I select count(*) from both tables for this value they both return 14 rows each. Why is it reporting that 6000 rows have been updated?
UPDATE
table1 A
SET
phone_work = (
SELECT B.phone_work
FROM table2 B
WHERE B.id = A.applicant_id)
WHERE EXISTS (
SELECT 1
FROM table2 B
WHERE B.id = A.applicant_id);
I have also tried the following and I get the same result:
UPDATE
table1 A
SET
phone_work = (
SELECT B.phone_work
FROM table2 B
WHERE B.id = A.applicant_id
AND B.phone_work is not null
)
WHERE EXISTS (
SELECT 1
FROM table2 B
WHERE B.id = A.applicant_id);
Why is it reporting the update of 6000 rows? When I change the fields but use the same syntax it reports updating of the exact number of rows I expect e.g. a count of table B has 86 entries in the NAME field and it reports 86 rows updated. It seems that with the phone_work field I am getting every null value being counted as an update.