Etiket arşivi: count

SQL de bir tablodaki duplicate kayıtları bulma

Yayın tarihi
Cevapla

SELECT A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR
FROM IASMATVENDORS A
GROUP BY A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR
HAVING COUNT(*)>1;

SELECT A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR
INTO TMPIASMATVENDORS
FROM IASMATVENDORS A
GROUP BY A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR
HAVING COUNT(*)>1;

SELECT A.* FROM IASMATVENDORS A,TMPIASMATVENDORS B
WHERE A.CLIENT=B.CLIENT AND A.COMPANY=B.COMPANY AND A.MATERIAL=B.MATERIAL AND A.VENDOR=B.VENDOR
AND VALIDFROM >’2009-01-01′;

DELETE A FROM IASMATVENDORS A,TMPIASMATVENDORS B
WHERE A.CLIENT=B.CLIENT AND A.COMPANY=B.COMPANY AND A.MATERIAL=B.MATERIAL AND A.VENDOR=B.VENDOR
AND VALIDFROM >’2009-01-01′;

UPDATE IASMATVENDORS SET VALIDFROM =’2005-01-01′ WHERE MATERIAL LIKE ‘%’ AND VALIDFROM <>’2005-01-01′;

DROP TABLE TMPIASMATVENDORS;