SQL Query is updating with NULL values

Question

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.

Answer 1

Perhaps you want to check for the non-NULL value in the exists:

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 AND B.phone_work IS NOT NULL
                 );

Answer 2

Try this:

UPDATE
(
    SELECT A.phone_work Aphone, B.phone_work Bphone 
    FROM table2 B, table1 A 
    WHERE B.id = A.applicant_id AND B.phone_work IS NOT NULL
)
SET
Aphone = Bphone;

Source: Oracle SQL: Update a table with data from another table