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.