Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

trifek's avatar

Update query for duplicates

Hi, I have small problem with my SQL Update query.

I have this code:

UPDATE records set kp= -kp
                                            where av=pm and secret_key = 123 and date='2014-01-21' and pm IN (SELECT pm FROM 
                                            (SELECT pm, 
                                            ROW_NUMBER() OVER(PARTITION BY secret_key 
                                            ORDER BY secret_key ASC) AS row_num
                                            FROM records ) platnosc
                                            WHERE platnosc.row_num > 1 and secret_key =1 and dt_payment_date='2014-01-21');

The above code should only work when:

  1. I have duplicates (more than 1 record with data with the same parameters date and secret_key)
  2. I need update only records when i have more that one.

When i run this code more that one time - i need updated only duplicates.

Now that code work fine, but when i run this code second time - this sql update me all records.

Hiw can i fix it?

Please help me.

0 likes
1 reply
hupp's avatar

@trifek

Try add AND kp > 0 in the where condition

where av=pm and secret_key = 123 and date='2014-01-21' and kp > 0 and pm IN

Please or to participate in this conversation.