When I update one table from another, lots of my data turns to nulls - what have I done wrong ?

The Oracle (tm) Users' Co-Operative FAQ

When I update one table from another, lots of my data turns to nulls - what have I done wrong ?

The problem here is that you have probably written some SQL to update data, without remembering that sometimes the associated data may be missing. The code sample that shows this problem usually looks something like:

update tableX	tx
set colA = (
		select sum(colB)
		from	tableY	ty
		where	ty.ref_col = tx.ref_col
	)
;

But what will happen if there are no matching rows in TableY for a given row in TableX ? The subquery finds no data. You might expect this to result in Oracle returning the error "no data found". In fact Oracle simply returns a null value - so if colA originally held a value, it will disappear.

To circumvent this problem, the simplest change is to modify the query so that it reads - update table X if there is any data. You do this by adding a where clause (that looks very similar to the subquery) to restrict the update to those rows for which relevant data exist. For example:

update tableX	tx
set colA = (
	select sum(colB)
	from	tableY	ty
	where	ty.ref_col = tx.ref_col
	)
where exists (
	select	null
	from	tableY	ty
	where	ty.ref_col = tx.ref_col
)
;

Note - this is not the only SQL solution for this type of update - take a look, for example, at updatable join views, which are typically twice as efficient as this subquery method.

Further reading: N/A