2 sütun sharti bilan ikki nusxadagi qiymatlarni sql-serverdan olib tashlash

|Rownumber  |OldIdassigned  |commoncode  |
------------------------------------------
| 1         |FLEX           |Y2573F102   |
------------------------------------------
| 2         |RCL            |Y2573F102   |
------------------------------------------
| 3         |FLEX           |Y2573F102   |
------------------------------------------
| 4         |QGEN           |N72482123   |
------------------------------------------
| 5         |QGEN           |N72482123   |
------------------------------------------
| 6         |QGEN           |N72482123   |
------------------------------------------
| 7         |RACE           |N72482123   |
------------------------------------------
| 8         |CLB            |N22717107   |
------------------------------------------
| 9         |CLB            |N22717107   |
------------------------------------------
| 10     |CLB            |N22717107   |


I need to delete the duplicate records based on Common code and a condition that - if oldidassigned is same then delete else don't delete.

Misol uchun, Y2573F102 uch dona replikatsiya ro'yhatiga ega 1,2,3 .... 1,2 o'chirilmasligi kerak, faqat uchinchi qator o'chiriladi.

1

5 javoblar

Shu maqsadda yangilanadigan CTE va oyna vazifalarini yaxshi ko'raman:

with todelete as (
      select t.*,
             row_number() over (partition by commoncode order by rownumber) as seqnum
      from t
     )
delete todelete
    where seqnum > 1;
5
qo'shib qo'ydi
@chadalavadaharish. . . Savolni noto'g'ri deb bilaman. Siz "ikki ustun" holatini bildirasiz. Lekin, aslida, uchinchi ustun uchun eng qadimgi yozuvni xohlaysizmi. Bu ikkinchi ustunni tartibini yordamida olib tashlash bilan belgilanadi.
qo'shib qo'ydi muallif Gordon Linoff, manba
kutilganidek ishlaganingiz uchun rahmat
qo'shib qo'ydi muallif chadalavada harish, manba

ROW_NUMBER() dan foydalaning:

DELETE t
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY OldIdassigned, commoncode ORDER BY rownumber) AS Seq
      FROM table t
     ) t
WHERE t.seq > 1;

EDIT: Agar kodni commoncode ga asoslangan holda takrorlashni tekshirishni istasangiz, PARTITION dan OldIdassigned ni olib tashlang:

DELETE t
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY commoncode ORDER BY rownumber DESC) AS Seq
      FROM table t
     ) t
WHERE t.seq > 1; 
2
qo'shib qo'ydi
@chadalavadaharish. . . Partiya atamasida faqatgina commoncode kerak.
qo'shib qo'ydi muallif Yogesh Sharma, manba
ishlamadi nol
qo'shib qo'ydi muallif chadalavada harish, manba

Sizning tavsifingizga va sharhlarga ko'ra, oyna kodi funktsiyasidan foydalaning row_number

delete t
from
(select t1.*,row_number() over(partition by commoncode order by  Rownumber) rn from table t1
)t where rn<>1

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=eacc0688efb534a0addee68678f323fe

2
qo'shib qo'ydi
@chadalavadaharish dbfiddle.uk/… ushbu havola tekshirish
qo'shib qo'ydi muallif Zaynul Abadin Tuhin, manba
@chadalavadaharish bu sizning chiqishingizni amalga oshiradi Rownumber OldIdassigned umumiy kod 1 FLEX Y2573F102 4 QGEN N72482123 8 CLB N22717107
qo'shib qo'ydi muallif Zaynul Abadin Tuhin, manba

Row_Number() dan foydalaning

delete t from 
(select *, row_number() over(partition by commoncode order by 
 rownumber) as rn) t 
 where rn<>1
2
qo'shib qo'ydi

Barcha javoblar o'xshash bo'lgani uchun (va to'g'ri), men bir muqobil yo'lni yuboraman:

DELETE FROM TableA
WHERE EXISTS ( SELECT * FROM TableA AS A2
               WHERE A2.commoncode = TableA.commoncode
                 AND A2.OldIdassigned = TableA.OldIdassigned
                 AND A2.Rownumber < TableA.Rownumber )
1
qo'shib qo'ydi
kutilganidek ishlaganingiz uchun rahmat
qo'shib qo'ydi muallif chadalavada harish, manba