| Loading duplicate rows into an exception table [message #38743] |
Fri, 10 May 2002 01:50  |
Ananya
Messages: 3
Registered: April 2002
|
Junior Member |
|
|
I have duplicate rows present in a table which doesnt have any constraint keys present.
How can I move all the duplicate rows into an exception table so that I can generate the contraints on my table?
I need the duplicate records into an exception table as I need to keep track of them.
The below command only allows me to delete the duplicates.
delete from test where rowid in
(select rowid from test
minus
select min(rowid) from test group by a,b);
How can I insert all duplicate rows into an exception table?
Any help will be appreciated.
|
|
|
|
| Re: Loading duplicate rows into an exception table [message #38745 is a reply to message #38743] |
Fri, 10 May 2002 03:59  |
Rick Cale
Messages: 111
Registered: February 2002
|
Senior Member |
|
|
You pretty much answered your own question.
INSERT into exceptions table by the following
INSERT INTO exceptions
SELECT * from test where rowid in
(select rowid from test
minus
select min(rowid) from test group by a,b);
or if exceptions does not exist
CREATE TABLE exceptions AS
SELECT * from test where rowid in
(select rowid from test
minus
select min(rowid) from test group by a,b);
Now delete them
delete from test where rowid in
(select rowid from test
minus
select min(rowid) from test group by a,b);
Rick
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
