FORALL in Oracle



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
d27m11yAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Swadhin RayConnect With a Mentor Senior Technical Engineer Commented:
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
 
awking00Commented:
Are they always just duplicates or can there also be triplicates, for example?
0
 
d27m11yAuthor Commented:
helpful!
0
All Courses

From novice to tech pro — start learning today.