Help with Update Statement

[OracleDBAAndDev]

Hey Folks,

I've got two tables. Both are structurally identical. One is fed via sql loader (load table) with data from another system, and the other (the production table), is updated based on differences from the sqlloaded table. The only changes are in one column. When I do an UPDATE with a JOIN and a WHERE clause, it gets all records, not the filtered records I'm looking for. Can you help?

This is the UPDATE that I'm trying to run:

update PRODUCTION_TABLE A
set COLUMN_1 = (
select COLUMN_1
from LOAD_TABLE B
where A.PK_COLUMN = B.PK_COLUMN
and A.COLUMN_1 is null
and B.COLUMN_1 is not null);

This updates all joined records, and seems to ignore the WHERE A is null and B is not null.

When I select them in a join, the appropriate number of records are returned.


select *
from PRODUCTION_TABLE A, LOAD_TABLE B
where A.PK_COLUMN = B.PK_COLUMN B
and A.COLUMN_1 is null
and B.COLUMN_1 is not null;

Thanks for your help.

[skhanal]

You need to put the filter outside of set clause as well.

update PRODUCTION_TABLE A
set COLUMN_1 = (
select COLUMN_1
from LOAD_TABLE B
where A.PK_COLUMN = B.PK_COLUMN
and A.COLUMN_1 is null
and B.COLUMN_1 is not null)
where exists (select 1 from LOAD_TABLE B
where A.PK_COLUMN = B.PK_COLUMN
and A.COLUMN_1 is null
and B.COLUMN_1 is not null)