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