Solved

FORALL in Oracle

Posted on 2011-09-27
3
341 Views
Last Modified: 2012-05-12


I have the following sample data and I just want to swap only the membereligids for the same fname,lname and dob
keeping the rest of the data as it is. The issue is there is unique constraint on the column Membereligids.

Please see the attached sample data

I am not sure how I can develop logic using CURSORS/FORALL. Can someone guide me ?

Eg: Fname - Amit
 Lname - Kumar
  has two records with the same DOB but with different membereligids. I want to swap only 40681910,40813962

Sample-data.xls
0
Comment
Question by:d27m11y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 36711091
Are they always just duplicates or can there also be triplicates, for example?
0
 
LVL 16

Accepted Solution

by:
Swadhin Ray earned 500 total points
ID: 37181802
Assuming there will be always duplicity based on your sample data provided.

Check the code as below:

 
--Sample table 
create table swap 
(
id number,
name varchar2(40),
dob date,
gid number
);

-- insert query 
insert into swap values(1,'NAME1','19-NOV-1959',101);
insert into swap values(2,'NAME1','19-NOV-1959',102);
insert into swap values(3,'NAME2','19-OCT-1959',103);
insert into swap values(4,'NAME2','19-OCT-1959',104);
COMMIT;


--update query
UPDATE swap a
   SET gid = (SELECT gid
                FROM swap b
               WHERE b.NAME = a.NAME AND a.ROWID <> b.ROWID);

Open in new window

0
 

Author Closing Comment

by:d27m11y
ID: 37200670
helpful!
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question