ข้ามไปที่เนื้อหาหลัก
Windows Dev Center
ลงชื่อเข้าใช้

 locked
T-SQL to Update duplicate records RRS feed

  • คำถาม

  • Ok Im not sure this can even be done but I have a table that has a field with duplicates in it, 970 of them to be exact.

    What Id like to do is within this field, find duplicate entries and replace one of the entries by adding an 'A' or 'B' to the front or end of the string so it makes it not a duplicate. What Ive noticed is that it may not just be duplicates there may be as many as 4 of the same entries in this field.

    The tricky part is that there are good entries in this field already so I cant update the entire database, I have the ID's specific to this update, so I only need the 970 entries updated.

    How can I write this? How can I write a script to use the 970 ID's and only change one of the duplicate entries by adding a letter in front of it?

    Thanks!


    Russ, MCSE

    29 พฤษภาคม 2555 16:19

คำตอบ

  • See Uri's blog post. You can use the CTE ROW_NUMBER solution to update the duplicates only, e.g. something like

    WITH Dups AS
    (
      SELECT *, ROW_NUMBER() OVER (PARTITION unqiueColumns ORDER BY whateverOrder) AS RN
      FROM yourTable
    )
    UPDATE Dups 
    SET uniqueColumns = uniqueColumns + CAST(RN AS VARCHAR(255))
    WHERE RN > 1;

    • เสนอเป็นคำตอบโดย Naomi N 29 พฤษภาคม 2555 16:37
    • ทำเครื่องหมายเป็นคำตอบโดย Kalman Toth 4 มิถุนายน 2555 14:30
    29 พฤษภาคม 2555 16:24

ตอบทั้งหมด