Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

FORALL in Oracle

Posted on 2011-09-27
3
Medium Priority
?
346 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
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 1500 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

885 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