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.

Can you add some sample table data, and it's expected result? — jarlh, Jan 28 '16 at 13:27

score 1 · Answer 1 · answered Jan 28 '16 at 13:30

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
                 );

answered Jan 28 '16 at 13:30

Gordon Linoff

1,242,037
58
646
786

thanks, that has worked. Any idea why it needs this in the WHERE EXISTS section rather than the first SELECT statement? – HardLeeWorking Jan 28 '16 at 14:04
1

@HardLeeWorking . . . Yes. The `WHERE` clause determines which rows are updated. The `SET` statement determines the values being set in the rows. – Gordon Linoff Jan 28 '16 at 20:04

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

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

edited May 23 '17 at 11:44

Community

1
1

answered Jan 28 '16 at 14:11

P. Jairaj

1,033
1
6
8

SQL Query is updating with NULL values

2 Answers2