CODEWITHSUNDEEP
google-bigquery
dumb_coder
dumb_coder

Reputation: 325

Merge statement to remove duplicate values

I am trying to remove the duplicate entries as following

Table abc

id name service_file_id
1   abc xyz
2   vbg xyz
2   vbg xyz
3   kio xyz
3   kio xyz
4   abc yzx
5   nji yzx
6   pop yzx

Table temp_table
service_file_id file_id
xyz             null
yzx             file_name_1

The table abc has duplicate entries for example

id name service_file_id
2  vbg  xyz
2  kio  xyz

I get the information about the duplicate entries via temp table. When a file_id in temp table is null, it means I have duplicate entries for that particular serivce_file_id

Below is the code I have tried

merge `abc` t

using (
  # choose a single row to delete the duplicates
  SELECT distinct a.* 
    FROM `abc` a
    left join `temp_table` b
    on a.service_file_id = b.file_id
    where b.file_id is not null

  )


ON FALSE
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT ROW

I can remove the duplicate entries from the table but it also deletes the rest of the data which is not duplicate.

current output
id name service_file_id
1  abc  xyz
2  vbg  xyz
3  kio  xyz

Desired output
id name service_file_id
1  abc  xyz
2  vbg  xyz
3  kio  xyz
4  abc  yzx
5  nji  yzx
6  pop  yzx

Could you please help me out here?

Thanks in advance!!

Upvotes: 0

Views: 1659

Answers (1)

Anjela B
Anjela B

Reputation: 1201

Without actually referencing the temp_table table you can delete the duplicates in your abc table just by using merge and select distinct :

merge `abc` t

using(
SELECT distinct a.*
    FROM `abc` a
)

ON FALSE
WHEN NOT MATCHED BY TARGET THEN INSERT ROW
WHEN NOT MATCHED BY SOURCE THEN DELETE

Output:

enter image description here

Upvotes: 1

Related Questions