Update Query For Duplicate Records Oracle

Question

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;

As per my knowledge you need to take help of PL/SQL single update query won't work — codeLover
– codeLover, Commented Jul 5, 2018 at 4:53
@GarimaGupta : There is no reason to make such a conclusion. — Kaushik Nayak
– Kaushik Nayak, Commented Jul 5, 2018 at 5:14

Kaushik Nayak · Accepted Answer · 2018-07-05 05:21:20Z

1

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);

edited Jul 5, 2018 at 5:21

answered Jul 5, 2018 at 5:13

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update Query For Duplicate Records Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related