CODEWITHSUNDEEP
sqloracle-database
Hudhaifa Yoosuf
Hudhaifa Yoosuf

Reputation: 919

Update Query For Duplicate Records Oracle

I am trying to write an update statement for the below given scenario.

card_no is the primary key of the table and civil number is the data.

the output column has to be updated with the query like below.

This is the select query which i wrote to get the data arranged like this. but i need tip update output column like the image below.

this select statement will derive only duplicated values it can be 2 times or more, so based on the number of duplication D string should be appended to the output with the civil number..

No idea on where to start, some help would be appreciated. 

SELECT LC.CARD_NO,LC.CIVIL_NO
FROM CARD LC
JOIN DUPLICATE_CARD ST2 ON LC.CIVIL_NO=ST2.CIVIL_NO
WHERE  LC.CIVIL_NO IS NOT NULL 
ORDER BY LC.CIVIL_NO;

enter image description here

Upvotes: 0

Views: 477

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You may use the count analytic function with LPAD

SELECT card_no
    ,LPAD('D', count(civil_no) OVER (
            PARTITION BY civil_no ORDER BY card_no
            ), 'D') || civil_no as output
FROM t;

Demo

It's not clear which table you want to update, you may do so with a correlated update using the above select or a MERGE INTO

UPDATE t t1 
SET    output = (SELECT output 
                 FROM   (SELECT card_no, 
                                lpad('D', COUNT(civil_no) 
                                            over ( 
                                              PARTITION BY civil_no 
                                              ORDER BY card_no ), 'D') 
                                || civil_no AS output 
                         FROM   t) t2 
                 WHERE  t1.card_no = t2.card_no);

Upvotes: 1

Related Questions