CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Jenifer
Jenifer

Reputation: 347

How to update the duplicate records instead of deleting them

I have a table named T1 with columns - t_id, t_id_pk,t_version_ind. t_id_pk is the primary key column. t_version_ind is the Flag column with values 'TRUE' and 'FALSE'.

I have duplicate records in the table T1.

column t_version_ind is having incremental values. I have duplicate records with same t_id values repeated multiple times. I also have duplicate records with t_version_ind as 'TRUE'.

When I do a select, I'm having multiple records like this:

SELECT t_id,count(*) FROM T1 WHERE t_version_ind ='TRUE'
GROUP BY t_id 
HAVING count(*)>1;
t_id duplicate COUNT
100 2
101 2
102 3
103 2
104 4
105 5

SELECT * FROM T1 WHERE t_id='100' and t_version_ind ='TRUE', I'm getting duplicate records.

I don't want to delete the records which are duplicated. For one particular ID, I need to display only one row with t_version_ind as 'TRUE'. I need to update the t_version_ind for the remaining duplicate rows to FALSE.

Need help in forming a SQL query using UPDATE statement. Thanks in advance!

| t_id_pk | t_id | duplicate COUNT| | -------- | -------- | ---------------- 53066 960023712 TRUE 7458 960023712 TRUE 604475 960023712 TRUE 604538 960023712 TRUE 604539 960023712 TRUE 604664 960023712 TRUE 599867 960023712 TRUE 599868 960023712 TRUE 599869 960023712 TRUE 599929 960023712 TRUE 599998 960023712 TRUE 1414272 960023712 TRUE

117051 960089122 TRUE 53706 960089122 TRUE 116926 960089122 TRUE 604635 960089122 TRUE 604636 960089122 TRUE 600018 960089122 TRUE 600019 960089122 TRUE 597652 960089122 TRUE 604312 960089122 TRUE 604313 960089122 TRUE 604375 960089122 TRUE 609430 960089122 TRUE 609496 960089122 TRUE 609497 960089122 TRUE 596367 960089122 TRUE |

This is the sample data I get when I query the table. I need to update huge records. Can you pls guide?

Upvotes: 1

Views: 2050

Answers (1)

Scinana
Scinana

Reputation: 402

(Can only answer based on the info you've provided on your question up to now)

You could group by id as you are already doing and get the max t_id_pk (assuming they are integers), then feed those max primary keys into an update query, excluding them from processing.

Use that update query to turn all the remaining t_version_ind into FALSE.

This is best explained in the following code:

UPDATE T1 
SET t_version_ind = 'FALSE'
where t_id_pk NOT IN (
    SELECT t_id_pk FROM 
        (
        SELECT t_id, MAX(t_id_pk) as t_id_pk FROM T1 
        WHERE t_version_ind ='TRUE'
        GROUP BY t_id 
        HAVING count(*)>1)
        )

Upvotes: 1

Related Questions