Introduction
Usually there are situations where we may be required to
update those records of a table, which are found equal in some other table. But
simply doing an update on the target table will cause the non-matching records
updated to null. Here the use of "where exists" clause can avoid
ambiguity by letting the non-matching records remain as before.
Requirements
Oracle database 9i
Problem Description
The above stated problem can be represented as below. Let us
say we have a source table and a target table which will get updated consisting
of two fields each as NAME and ID.
Source Table:
DEPT
Figure 1
Destination Table:
EMP
Figure 2
Let us say we are required to update the NAME field EMP
table for those ID's found common to ID's of DEPT table. This simply means we
want the EMP table to get updated as given below.
ID NAME
----------
1 E
2 F
3 G
5 X
The solution seems quite straightforward from the surface.
As a result, the most obvious looking solution creates the problem explained
below.
Listing 1
update EMP a set a.name =(select name from DEPT b where a.id=b.id );
Figure 3
But we never wanted to get a null for the ID= 5. The query
in Listing1 not only updates the NAME for common ID's, but it also updates the
NAME for non-matching ID as NULL, which is undesirable.
Solution Code
Listing 2
update EMP a set a.name =(select b.name from DEPT b where a.id = b.id )
where exists(select 1 from DEPT b where a.id = b.id);
Figure 4
Explanation
The query in listing 2 solves our problem by updating the
NAME for matching ID, but leaving non-matching ID as compared to the query in listing
1.
The reason behind this is simple. The query in listing1
tries to update the NAME field for all the records present in the table, which
forces it to update even those NAME the non-matching ID's to NULL.
Whereas the query in listing 2 tries to update the NAME
field for only those records whose ID's are found common and avoids updating
the NAME for non-matching ID's.
Conclusion
By following this approach the user can use update more
effectively and efficiently for similar situations.
