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
– jarlh, Commented Jan 28, 2016 at 13:27

Gordon Linoff · Accepted Answer · 2016-01-28 13:30:17Z

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, 2016 at 13:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HardLeeWorking Over a year ago

thanks, that has worked. Any idea why it needs this in the WHERE EXISTS section rather than the first SELECT statement?

Gordon Linoff Over a year ago

@HardLeeWorking . . . Yes. The WHERE clause determines which rows are updated. The SET statement determines the values being set in the rows.

Community · Accepted Answer · 2017-05-23 11:44:58Z

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, 2017 at 11:44

CommunityBot

11 silver badge

answered Jan 28, 2016 at 14:11

P. Jairaj

1,0331 gold badge6 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL Query is updating with NULL values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related