Home » SQL & PL/SQL » SQL & PL/SQL » DETECT AND MOVE DUPLICATE VALUES (2 threads merged by bb) (ORACLE 9i, WINDOWS 2003)
DETECT AND MOVE DUPLICATE VALUES (2 threads merged by bb) [message #447626] Tue, 16 March 2010 20:21 Go to next message
luvbondlength
Messages: 1
Registered: March 2010
Junior Member
Hi i am new to plsql need help. Have to create a procedure to solve the below problem.

Table contains duplicate data . Have to move data to another table. Criteria: check for duplicate values if duplicate exist move all duplicates except one to the history table. While moving to other table see if the record being moved already exists.


source table
SOURCE TABLE : ODS_OWNER
grp_id grp_name face_id address1 city zipcode

3456789 NIKE AERO 457899 707 CROFT GRAND RA 12345
1256789 NIKE AERO CORP 678899 707 CROFT SE GRAND RA 12345
5465455 BB SHIPPING 809708 201 SOUTH CT DESPLAINE 45434
3454534 BB SHIPPING INC 980900 201 SOUTH CT DESPLAINE 45434
2343444 CAT BRAKES 565665 P.O BOX 100 JERSEY 12323
3423423 CAT BRAKES 554645 200 CENTER DR CHICAGO 45645

FIRST 4 RECORDS ARE DUPLICATES FROM WHICH 1 RECORD GOES TO w_grp AND ONE GOES TO HISTORY TABLE. THE RECORD WHICH GOES INTO w_grp OUT OF THE DUPLICATES WILL DEPEND ON THE LAST MODIFIED DATE FOR EACH

DISTINCT VALUES GO IN w_grp TABLE

DUPLICATE GO INTO match_his TABLE
Re: DETECT AND MOVE DUPLICATE VALUES [message #447628 is a reply to message #447626] Tue, 16 March 2010 20:56 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ Please follow Posting Guidelines - OraFAQ Forum Guide
2/ Please provided DDL (CREATE TABLE ......) for ALL tables involved.
3/ Please provided DML (INSERT INTO .......) for Test data.
4/ Please provided expected/desired results.
5/ Please Preview Message / Spell-Check Before Posting.



1/ search how to select duplicate rows.
2/ create or insert above rows in table.
3/ convert 1/ select to delete duplicate rows from existing table.
Re: DETECT AND MOVE DUPLICATE VALUES (2 threads merged by bb) [message #447632 is a reply to message #447626] Tue, 16 March 2010 22:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
luvbondlength wrote on Tue, 16 March 2010 18:21

THE RECORD WHICH GOES INTO w_grp OUT OF THE DUPLICATES WILL DEPEND ON THE LAST MODIFIED DATE FOR EACH


I don't see any column that stores the last modified date in what you posted. If you do have such a column, then you can use an analytic function, partition by your unique criteria and order by the last modified date, to rank them by that date, and insert those with a rank of 1 into one table and all others into the duplicates table. You can find information on analytic functions in the online documentation. I suggest that your experiment with some select statements first, then create insert statements that use the select statements. There is no need to put that in a procedure, but you can if required. This is just one method. There are always various ways to do things.

Re: DETECT AND MOVE DUPLICATE VALUES (2 threads merged by bb) [message #447694 is a reply to message #447626] Wed, 17 March 2010 07:22 Go to previous message
tejas.patel
Messages: 22
Registered: December 2008
Location: NJ
Junior Member

hi,
This is hint of comes a duplicate records. so further you try and create S.P. that record insert into another table. thats table in check contraint put on field.

SELECT count( * ) , grp_id,grp_name
FROM ODS_OWNER
GROUP BY grp_id,grp_name
HAVING COUNT( * ) >1

tejas patel
Previous Topic: execution time of a query
Next Topic: pivot query
Goto Forum:
  


Current Time: Sun Dec 11 08:10:23 CST 2016

Total time taken to generate the page: 0.04538 seconds