Delete/Update duplicate records 2

[yorge]

Hi Guys,

Given the table and rows on it below:

MYTABLE
-----------------
CODE varchar(30)
NAME varchar(100
DESCRIPTION(300)
-----------------

CODE NAME DESCRIPTION
----------------------------------------------------------------------------------------------------------------------------------------------
null Cozaar Cozaar Tab 50 mg
null Cozaar Cozaar Tab 100 mg
null Cozaar Cozaar Pulv og væske til mikst 2,5 mg/ml
null Cozaar Cozaar Tab 12,5 mg
null Cozaar Cozaar Tab 50 mg
null NatriumhydrogenkarbonatBraunplus Nycoplus calcium m/K & D3-vitamin Tab 75 mikrog/5 mikrog/250 mg
null Skelan Skelan 500 mg
null Detox Detox 100mg
null Skelan Skelan 500 mg
null NatriumhydrogenkarbonatBraunplus Nycoplus calcium m/K & D3-vitamin Tab 75 mikrog/5 mikrog/500 mg
null BenzylpenicillinVMpanpharmaSA Benzylpenicillin VM panpharma SA Pulv og væske til inf væske 1,2 g
----------------------------------------------------------------------------------------------------------------------------------------------


If duplicate NAME, will then check if duplicate description.
If description is not duplicate then update CODE = 'GMD_' + upper(NAME) + '1'... and so on (but since CODE is varchar(30), substr NAME to fit in the CODE field
if duplicate NAME and DESCRIPTION then delete duplicate row(s)
If unique then CODE = 'GMD_' || upper(substr(name,1,25));

Final rows after the changes will be like this:

CODE NAME DESCRIPTION
----------------------------------------------------------------------------------------------------------------------------------------------
GMD_COZAAR1 Cozaar Cozaar Tab 50 mg
GMD_COZAAR2 Cozaar Cozaar Tab 100 mg
GMD_COZAAR3 Cozaar Cozaar Pulv og væske til mikst 2,5 mg/ml
GMD_COZAAR4 Cozaar Cozaar Tab 12,5 mg
GMD_SKELAN Skelan Skelan 500 mg
GMD_DTOX Detox Detox 100mg
GMD_NATRIUMHYDROGENKARBONAT1 NatriumhydrogenkarbonatBraunplus Nycoplus calcium m/K & D3-vitamin Tab 75 mikrog/5 mikrog/250 mg
GMD_NATRIUMHYDROGENKARBONAT2 NatriumhydrogenkarbonatBraunplus Nycoplus calcium m/K & D3-vitamin Tab 75 mikrog/5 mikrog/500 mg
GMD_BENZYLPENICILLINVMPANPHA BenzylpenicillinVMpanpharmaSA Benzylpenicillin VM panpharma SA Pulv og væske til inf væske 1,2 g
----------------------------------------------------------------------------------------------------------------------------------------------

Can anybody suggest a query to do this?

TIA
Yorge

 

[yorge]

im not sure what happened to the indents on the sample rows but NAME columns values will be:

NAME
---------------------------------
Cozaar
Cozaar
Cozaar
Cozaar
Skelan
Detox
NatriumhydrogenkarbonatBraunplus
NatriumhydrogenkarbonatBraunplus
BenzylpenicillinVMpanpharmaSA
---------------------------------

 

[johnherman]

To me, this looks like something that would be best done in a cursor rather than a single query. The resetting of the incremental counter when then Name changes is particularly difficult. Do you understand cursors? If not, someone will provide a sample or a link to a tutorial.

==================================
adaptive uber data solutions for info galaxies (bigger, better, faster than agile big data clouds)

 

[yorge]

yup, I do understand cursor but was hoping for a solution that will not involve cursor - but all possible suggestions are welcome and hoping for some sample as well later...tnx for the reply johnherman

 

[LKBrwnDBA]

Try this:

SQL> WITH Mytab1
  2       AS (SELECT NULL Code, 'Cozaar' Name, 'Cozaar Tab 50 mg' Description FROM DUAL UNION ALL
  3           SELECT NULL, 'Cozaar', 'Cozaar Tab 100 mg' FROM DUAL UNION ALL
  4           SELECT NULL, 'Cozaar', 'Cozaar Pulv og væske til mikst 2,5 mg/ml' FROM DUAL UNION ALL
  5           SELECT NULL, 'Cozaar', 'Cozaar Tab 12,5 mg' FROM DUAL UNION ALL
  6           SELECT NULL, 'Cozaar', 'Cozaar Tab 50 mg' FROM DUAL UNION ALL
  7           SELECT NULL
  8                , 'NatriumhydrogenkarbonatBraunplus'
  9                , 'Nycoplus calcium m/K # D3-vitamin Tab 75 mikrog/5 mikrog/250 mg'
 10             FROM DUAL UNION ALL
 11           SELECT NULL, 'Skelan', 'Skelan 500 mg' FROM DUAL UNION ALL
 12           SELECT NULL, 'Detox', 'Detox 100mg' FROM DUAL UNION ALL
 13           SELECT NULL, 'Skelan', 'Skelan 500 mg' FROM DUAL UNION ALL
 14           SELECT NULL
 15                , 'NatriumhydrogenkarbonatBraunplus'
 16                , 'Nycoplus calcium m/K # D3-vitamin Tab 75 mikrog/5 mikrog/500 mg'
 17             FROM DUAL UNION ALL
 18           SELECT NULL
 19                , 'BenzylpenicillinVMpanpharmaSA'
 20                , 'Benzylpenicillin VM panpharma SA Pulv og væske til inf væske 1,2 g'
 21             FROM DUAL)
 22  -- ---------------------------------
 23  SELECT 'GMD_' || UPPER ( Name ) || DENSE_RANK ( )
 24            OVER ( PARTITION BY Name ORDER BY Description ) Code
 25       , T.Name
 26       , T.Description
 27*   FROM (SELECT DISTINCT * FROM Mytab1) T
SQL> /

CODE                                     NAME                                     DESCRIPTION
---------------------------------------- ---------------------------------------- --------------------------------------------------
GMD_BENZYLPENICILLINVMPANPHARMASA1       BenzylpenicillinVMpanpharmaSA            Benzylpenicillin VM panpharma SA Pulv og væske
                                                                                  til inf væske 1,2 g

GMD_COZAAR1                              Cozaar                                   Cozaar Pulv og væske til mikst 2,5 mg/ml
GMD_COZAAR2                              Cozaar                                   Cozaar Tab 100 mg
GMD_COZAAR3                              Cozaar                                   Cozaar Tab 12,5 mg
GMD_COZAAR4                              Cozaar                                   Cozaar Tab 50 mg
GMD_DETOX1                               Detox                                    Detox 100mg
GMD_NATRIUMHYDROGENKARBONATBRAUNPLUS1    NatriumhydrogenkarbonatBraunplus         Nycoplus calcium m/K # D3-vitamin Tab 75 mikrog/5
                                                                                  mikrog/250 mg

GMD_NATRIUMHYDROGENKARBONATBRAUNPLUS2    NatriumhydrogenkarbonatBraunplus         Nycoplus calcium m/K # D3-vitamin Tab 75 mikrog/5
                                                                                  mikrog/500 mg

GMD_SKELAN1                              Skelan                                   Skelan 500 mg

9 rows selected.

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[taupirho]

Here's something to get you started - I'm not doing everything for you.

select * from tom
/

CODE NAME DESCRIPTION
------------------------------ ------------------------------ ------------------------------
TOM DESC1
TOM DESC1
TOM DESC1
TOM DESC2
TOM DESC3
DICK DESC1
HARRY DESC4

The delete is trivial

1 delete from tom where rowid in
2 (
3 select rowid from
4 (
5 select rowid,code,name,description,
row_number() over(partition by name,description order by code,descripion) rn
6 from tom
7 )
8 where rn > 1
9* )
QL> /

2 rows deleted.

QL> select * from tom;

CODE NAME DESCRIPTION
----------------------------- ------------------------------ ------------------------------
TOM DESC1
TOM DESC2
TOM DESC3
DICK DESC1
HARRY DESC4

SQL>


And to get your code for update , use this as a basis

SQL> l
1 select 'GMD_' || name || row_number() over(partition by name order by name,description) code,
2 name,description
3* from tom
SQL> /

CODE NAME DESCRIPTION
------------------------------ ------------------------------ ------------------------------
GMD_DICK1 DICK DESC1
GMD_HARRY1 HARRY DESC4
GMD_TOM1 TOM DESC1
GMD_TOM2 TOM DESC2
GMD_TOM3 TOM DESC3


In order to understand recursion, you must first understand recursion.

 

[johnherman]

kudos and a star to LKBrwnDBA for the sweet solution using DENSE_RANK

==================================
adaptive uber data solutions for info galaxies (bigger, better, faster than agile big data clouds)

 

[yorge]

thanks LKBrwnDBA for the help...sorry late reply, was sick :-(