Senin, 03 April 2023

NOTE DUPLICATE DATA BY QUERY

 

QUERY UNTUK MENGATASI DUPLICATE DATA


# SELECT BY HAVING COUNT

Di gunakan untuk mencari data yang duplicate berdasarkan count group name,
jadi kita bisa kasih parameter di bagian (having (count(name)) > 1)
 
SELECT name, COUNT(name)
FROM crm_lead
WHERE create_by_cron = true
AND schedule_date = '2023-03-11'
AND status_crm_pipeline isnull
AND active = true
GROUP BY name HAVING(COUNT(name)) > 1
 

# SELECT BY ROW_NUM

Di gunakan untuk mencari duplicate data berdasarkan row_number partition,

example data:

id      name        row_num

1       Slamet      1

2       Slamet      2

3       Slamet      3

4       SlametDev   1

5       SlametDev   2

 

result jika mengambil row 2:

id      name        row_num

2       Slamet      2

5       SlametDev   2

 

result jika mengambil row 3:

id      name        row_num

3       Slamet      3

 

SELECT id
FROM (
SELECT id,name, ROW_NUMBER() OVER (
PARTITION BY name ORDER BY id
) as row_num
FROM crm_lead
WHERE create_by_cron = true and schedule_date = '2023-03-11' and active = true
) t
WHERE row_num = 2

 

# UPDATE BY ROW_NUM

untuk mengupdate data menggunakan id yang di ambil dari select menggunakan row_number partition.

 

UPDATE crm_lead set active = false where id in (
SELECT id
FROM (
SELECT id,name, ROW_NUMBER() OVER (
PARTITION BY name ORDER BY id
) as row_num
FROM crm_lead
WHERE create_by_cron = true and schedule_date = '2023-03-11' and active = active
) t
WHERE row_num = 2
)

 

 

Tidak ada komentar:

Posting Komentar

flutter firebase notification