Home » SQL & PL/SQL » SQL & PL/SQL » rename duplicate records
rename duplicate records [message #190361] Wed, 30 August 2006 06:37 Go to next message
lijok
Messages: 68
Registered: April 2005
Member
Hi,
I need to rename the duplicate values which some how made an entry to the table temp_dochdr.
Here is the table with values. A unique constraint on (cny#, record#, docno, docid) should have prevented this, but
we failed to have this constraint on one of the db server.

I need to replace the duplicate docno and docid by appending with a '-1'
For example duplicate docno 'INV001' should become 'INV001-1' and docid 'Sales-INV001' as 'Sales-INV001-1'

How can I do this with an sql statment?

create table temp_dochdr(
cny#		number,
record#		number,
docno		varchar2(20),
docid		varchar2(20)
);
primarykey (cny#, record#)

insert into temp_dochdr values(100, 1, 'INV001', 'Sales-INV001');
insert into temp_dochdr values(100, 2, 'INV001', 'Sales-INV001');


insert into temp_dochdr values(101, 1, 'QOT001', 'QUOTE-QOT001');
insert into temp_dochdr values(101, 2, 'QOT001', 'QUOTE-QOT001');

insert into temp_dochdr values(101, 3, 'ORD001', 'ORDER-ORD001');



SQL> select count(docno), docid, cny#
  2  from temp_dochdr
  3  group by cny#, docid
  4  having count(*) > 1;

COUNT(DOCNO) DOCID                      CNY#
------------ -------------------- ----------
           2 Sales-INV001                100
           2 QUOTE-QOT001                101

Re: rename duplicate records [message #190390 is a reply to message #190361] Wed, 30 August 2006 08:10 Go to previous messageGo to next message
pareshr
Messages: 18
Registered: August 2006
Location: Ahmedabad
Junior Member
Hi

use follwing query to update..ur table

UPDATE temp_dochdr t1 SET t1.docno=t1.docno||'-I' , t1.docid = t1.docid||'-I' WHERE ROWID <(SELECT MAX(ROWID) FROM temp_dochdr
WHERE t1.cny# = temp_dochdr.cny# AND t1.RECORD# = temp_dochdr.RECORD#)

U will get result according to what u asked...

Re: rename duplicate records [message #190422 is a reply to message #190390] Wed, 30 August 2006 11:30 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
Hi,
Your query did not return the expected result because of this condition t1.RECORD# = temp_dochdr.RECORD# as record# combined with cny# returns unique rows.

However it helped me to think further in modifying the query to get the correct result.

Here is the output of both queries. Thanks a lot for your help.
SQL> UPDATE temp_dochdr t1 SET t1.docno=t1.docno||'-I' , t1.docid = t1.docid||'-I' 
  2  WHERE ROWID <(SELECT MAX(ROWID) FROM temp_dochdr
  3   WHERE t1.cny# = temp_dochdr.cny# AND t1.RECORD# = temp_dochdr.RECORD#)
  4  /

0 rows updated.

SQL> UPDATE temp_dochdr t1 
  2  SET t1.docno=t1.docno||'-I' 
  3  , t1.docid = t1.docid||'-I' 
  4  WHERE ROWID <(
  5   SELECT MAX(ROWID) FROM temp_dochdr
  6   WHERE t1.cny# = temp_dochdr.cny# 
  7   GROUP BY temp_dochdr.docid
  8   HAVING count(*) > 1
  9   )
 10  /

2 rows updated.

SQL> select * from temp_dochdr;

      CNY#    RECORD# DOCNO                DOCID
---------- ---------- -------------------- --------------------
       100          1 INV001-I             Sales-INV001-I
       100          2 INV001               Sales-INV001
       101          1 QOT001-I             QUOTE-QOT001-I
       101          2 QOT001               QUOTE-QOT001
       101          3 ORD001               ORDER-ORD001

Re: rename duplicate records [message #190470 is a reply to message #190422] Wed, 30 August 2006 23:36 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
I have another intereting case here, for a particular record there are 2 duplicates. The earlier query what was suggested will solve my problem as long as there is one duplicate.

In case if there are two duplicates, then it should append '-1' to the first duplicate and '-2' for the second duplicate.

SQL> select * from temp_dochdr;

      CNY#    RECORD# DOCNO                DOCID
---------- ---------- -------------------- -------------------
       100          3 INV001               Sales-INV001
       100          1 INV001               Sales-INV001
       100          2 INV001               Sales-INV001
       101          1 QOT001               QUOTE-QOT001
       101          2 QOT001               QUOTE-QOT001
       101          3 ORD001               ORDER-ORD001



In this case, 'INV001' has two duplicates. The first duplicate should be set to 'INV001-1' and the second duplicate as 'INV001-2' and the same way for docid too.

Can we achieve this in sql? or a PL/SQL code is required?

Thanks,
Lijo
Re: rename duplicate records [message #190474 is a reply to message #190470] Thu, 31 August 2006 00:02 Go to previous messageGo to next message
pareshr
Messages: 18
Registered: August 2006
Location: Ahmedabad
Junior Member

In my previous query i did mistake to in one condition that i have made some correction.

For ur second query u can use sequece to update more than one same record using only SQL as below given sql.

suppose u have sequence with name seq1 then fire following qury.

UPDATE temp_dochdr t1 SET t1.docno=t1.docno||'-' ||seq1.NEXTVAL , t1.docid = t1.docid||'-'||seq1.CURRVAL
WHERE ROWID <(SELECT MAX(ROWID) FROM temp_dochdr
WHERE t1.cny# = temp_dochdr.cny# AND t1.docno=temp_dochdr.docno)


But here it ll update all duplicate rows with unique number otherwise u can write PLSQL code for ur desired result.
Re: rename duplicate records [message #190476 is a reply to message #190474] Thu, 31 August 2006 00:16 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
Hi,
This will not work as I need the first duplicate to always be apppended with '-1'.

Experts, Any other way out in sql?

Thanks,
Lijo
Re: rename duplicate records [message #190494 is a reply to message #190361] Thu, 31 August 2006 01:26 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Here's one way (I have a feeling it could be simpler than this, but at least this works!):

SQL> SELECT * FROM temp_dochdr ORDER BY docno;

      CNY#    RECORD# DOCNO                DOCID
---------- ---------- -------------------- --------------------
       100          1 INV001               Sales-INV001
       100          2 INV001               Sales-INV001
       100          2 INV001               Sales-INV001
       101          3 ORD001               ORDER-ORD001
       101          1 QOT001               QUOTE-QOT001
       101          2 QOT001               QUOTE-QOT001

6 rows selected.

SQL> UPDATE temp_dochdr t1
  2  SET    t1.docno = t1.docno||'-'||(SELECT suffix FROM
  3                                     (SELECT (ROW_NUMBER() OVER 
  4                                             (PARTITION BY docno ORDER BY record#) - 1) suffix
  5                                      FROM temp_dochdr) t2
  6                                   WHERE t1.rowid = t2.rowid),
  7         t1.docid = t1.docid||'-'||(SELECT suffix FROM
  8                                     (SELECT (ROW_NUMBER() OVER 
  9                                             (PARTITION BY docno ORDER BY record#) - 1) suffix
 10                                      FROM temp_dochdr) t2
 11                                   WHERE t1.rowid = t2.rowid)
 12  WHERE  t1.rowid IN
 13         (SELECT LEAD(rowid) OVER
 14                  (PARTITION BY docno ORDER BY record#)
 15           FROM   temp_dochdr);

3 rows updated.

SQL> SELECT * FROM temp_dochdr ORDER BY docno;

      CNY#    RECORD# DOCNO                DOCID
---------- ---------- -------------------- --------------------
       100          1 INV001               Sales-INV001
       100          2 INV001-1             Sales-INV001-1
       100          2 INV001-2             Sales-INV001-2
       101          3 ORD001               ORDER-ORD001
       101          1 QOT001               QUOTE-QOT001
       101          2 QOT001-1             QUOTE-QOT001-1

6 rows selected.
icon10.gif  Re: rename duplicate records [message #190506 is a reply to message #190494] Thu, 31 August 2006 02:21 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
Wow that was really cool. This defnitely works. You can do magic with such sql knowledge. Hope I grow up soon.

But surely I would like to know if there is some other way too.. But, may be I'm asking more

Thanks a lot Hobbes
Re: rename duplicate records [message #190525 is a reply to message #190506] Thu, 31 August 2006 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hobbes,

you can tidy your solution up a bit by doing both the updates at once:

UPDATE temp_dochdr t1
SET    (t1.docno,t1.docid) = (SELECT t1.docno||'-'||t2.suffix
                                    ,t1.docid||'-'||t2.suffix FROM
                                   (SELECT (ROW_NUMBER() OVER (PARTITION BY docno ORDER BY record#) - 1) suffix
                                    FROM temp_dochdr) t2
                                 WHERE t1.rowid = t2.rowid)
WHERE  t1.rowid IN
       (SELECT LEAD(rowid) OVER
                (PARTITION BY docno ORDER BY record#)
         FROM   temp_dochdr);
Re: rename duplicate records [message #190526 is a reply to message #190525] Thu, 31 August 2006 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, you can remove the where clause from the UPDATE entirely with the judicious use of CASE

update temp_dochdr td1
set   (td1.docno,td1.docid) = (SELECT docno||(case when dup = 1 then null else '-'||(dup-1) end)
                                    , docid||(case when dup = 1 then null else '-'||(dup-1) end)
                               FROM   (SELECT docno
                                             ,docid
                                             ,rowid rid
                                             ,dense_rank() over (partition by cny#,docno,docid order by record#) dup
                                       FROM   temp_dochdr td2)
                               where rid = td1.rowid);
Re: rename duplicate records [message #190530 is a reply to message #190361] Thu, 31 August 2006 03:29 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
That's so much more elegant. Thanks!

The second solution would work with a slight change:
update temp_dochdr td1
set   (td1.docno,td1.docid) = (SELECT docno||(case when dup = 1 then null else '-'||(dup-1) end)
                                    , docid||(case when dup = 1 then null else '-'||(dup-1) end)
                               FROM   (SELECT docno
                                             ,docid
                                             ,rowid rid
                                             ,row_number() over (partition by docno order by record#) dup
                                       FROM   temp_dochdr td2)
                               where rid = td1.rowid);
Re: rename duplicate records [message #190534 is a reply to message #190530] Thu, 31 August 2006 03:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Glad you like it. I suspect your solution with the WHERE clause might perform better on a large update though.

Here's the results from my solution - is this not right?

SQL> update temp_dochdr td1
  2  set   (td1.docno,td1.docid) = (SELECT docno||(case when dup = 1 then null else '-'||(dup-1) end)
  3                                      , docid||(case when dup = 1 then null else '-'||(dup-1) end)
  4                                 FROM   (SELECT docno
  5                                               ,docid
  6                                               ,rowid rid
  7                                               ,dense_rank() over (partition by cny#,docno,docid order by record#) dup
  8                                         FROM   temp_dochdr td2)
  9                                 where rid = td1.rowid);

6 rows updated.

SQL> 
SQL> select * from temp_dochdr;

      CNY#    RECORD# DOCNO                DOCID
---------- ---------- -------------------- --------------------
       100          1 INV001               Sales-INV001
       100          2 INV001-1             Sales-INV001-1
       101          1 QOT001               QUOTE-QOT001
       101          2 QOT001-1             QUOTE-QOT001-1
       101          3 QOT001-2             QUOTE-QOT001-2
       101          3 ORD001               ORDER-ORD001

6 rows selected.
Re: rename duplicate records [message #190540 is a reply to message #190361] Thu, 31 August 2006 03:49 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
I was testing this with data in which the record numbers within a cny# are not unique, so was getting incorrect results.

On re-reading the OP I find that a PK already exists on (cny#, record#), so that condition will not arise - your query should work fine!
Re: rename duplicate records [message #190562 is a reply to message #190540] Thu, 31 August 2006 04:58 Go to previous message
lijok
Messages: 68
Registered: April 2005
Member
That was wonderful. Thanks for sharing your sql knowledge with me Smile

Thanks,
Lijo
Previous Topic: BitMap Index
Next Topic: can i create sequence for the existing records in the table
Goto Forum:
  


Current Time: Mon Dec 05 21:01:34 CST 2016

Total time taken to generate the page: 0.08940 seconds