CodeSnip: How to Update Records of a Table in Oracle Using Where Exists Clause
page 1 of 1
Published: 28 Mar 2008
Abstract
After reading this code snippet, you will know how to update records of a table in oracle. Deepankar makes use of the where exists clause to demonstrate the concept. He first provides a short overview of the problem and then provides the solution with the help of relevant SQL statements, screenshots and a short explanation.
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 31866/ 26

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

Picture 3

Destination Table:

EMP

Figure 2

Picture 1

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

Picture 2

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

Picture 3

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.



User Comments

Title: Good work   
Name: Aruna
Date: 2010-11-09 6:33:02 AM
Comment:
I was actually searching for it. Thanks!! Nice example. Keep going
Title: This help'd me!   
Name: Howard
Date: 2009-12-08 7:01:39 PM
Comment:
I needed to update some rows in a table which were similar to other rows in the same table ... because your example tag'd the table names as "a" and "b", I became confident that I could to this ... and it worked!

-- copy UCL from 197 to 198

update norm.parameter_limit a
set a.upper_control_limit_vl = (
select b.upper_control_limit_vl from norm.parameter_limit b where
a.param_limit_prod_scope_ky = 198 and b.param_limit_prod_scope_ky = 197 and a.parameter_cd = b.parameter_cd
)
WHERE EXISTS (
select 1 from norm.parameter_limit b where
a.param_limit_prod_scope_ky = 198 and b.param_limit_prod_scope_ky = 197 and a.parameter_cd = b.parameter_cd
and b.upper_control_limit_vl is not null
)

Thank-you!
Title: Good job   
Name: kiran
Date: 2009-05-29 11:21:28 AM
Comment:
This is great example. I m using this in many of my scenarios
this helps to update columns in a single table. ie update a column with another column in the same table for a single row

Kiran
Senior Software Engineering/DWH architect
Title: Good to see another Good Doc from u   
Name: Ajay
Date: 2008-03-24 12:13:36 PM
Comment:
Hi Deepankar,

Congrats for your new Doc and hope you will update with some more fundas in future.

Thanks,
Ajay

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-01-23 8:02:50 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search