SELECT DISTINCT FIRST, LAST, ADDRESS, CITY, STATE, ZIP, YEAR, MAKE, MODEL, COUNT(*) AS Count
	INTO Duplicate_Names
	FROM Namestest
	GROUP BY FIRST, LAST, ADDRESS, CITY, STATE, ZIP, YEAR, MAKE, MODEL
	HAVING (COUNT(*) > 1)

Delete From Namestest
	WHERE FIRST, LAST, ADDRESS, CITY, STATE, ZIP, YEAR, MAKE, MODEL
	IN (Select FIRST, LAST, ADDRESS, CITY, STATE, ZIP, YEAR, MAKE, MODEL
		From Duplicate_Names)

Insert Namestest
	Select *
		From Duplicate_Names

Drop Table Duplicate_Names

DELETE n
FROM
	NamesTest AS n
	JOIN Duplicate_Names AS dn ON
		dn.[FIRST] = n.[FIRST]
		AND dn.LAST = n.LAST
		AND dn.ADDRESS = n.ADDRESS
		AND dn.CITY = n.CITY
		AND dn.STATE = n.STATE
		AND dn.ZIP = n.ZIP
		AND dn.YEAR = n.YEAR
		AND dn.MAKE = n.MAKE
		AND dn.MODEL = n.MODEL

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

quote:

---

Originally posted by nigelrivett
Delete Namestest t
from Namestest t
join Duplicate_Names t2
on t.FIRST = t2.FIRST
and t.LAST = t2.LAST
and t.ADDRESS = t2.ADDRESS
and t.CITY = t2.CITY
and t.STATE = t2.STATE
and t.ZIP = t2.ZIP
and t.YEAR = t2.YEAR
and t.MAKE = t2.MAKE
and t.MODEL = t2.MODEL
....

---

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

quote:

---

Originally posted by Transact Charlie

Nigel -- think you posted a typo. Not sure if your original code would work or not (not deleting from alias)

quote:

---

Originally posted by nigelrivett
Delete Namestest t
from Namestest t
join Duplicate_Names t2
on t.FIRST = t2.FIRST
and t.LAST = t2.LAST
and t.ADDRESS = t2.ADDRESS
and t.CITY = t2.CITY
and t.STATE = t2.STATE
and t.ZIP = t2.ZIP
and t.YEAR = t2.YEAR
and t.MAKE = t2.MAKE
and t.MODEL = t2.MODEL
....

---

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

---

quote:

---

Originally posted by Transact Charlie

you could add a column with an identity value if you just wanted a surrogate pk.

So you don't have a primary key at the moment? It makes removing dups much easier.. If you had a UNIQUE CONSTRAINT over the columns in question then you could never get dups at all in the first place...........

---

quote:

---

Originally posted by nigelrivett

quote:

---

Originally posted by Transact Charlie

Nigel -- think you posted a typo. Not sure if your original code
....
....

---

Nope - that should work ok.
Only need to use the alias if the table is referenced more than once in the query otherwise it's more readable to use the original table name rather than the alias.
....

---

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

quote:

---

Originally posted by ncurran217

Ok so now when I ran the code the first two sets of code worked fine. But now when it tries to put the uniques back into the original table it cannot input them because I did not include all the columns in the first part. I have three columns I left out, LISTCODE, CUSTID and PROMO. LISTCODE could possibly be different in the duplicates, but CUSTID will be different (almost a primary key I would think), but I do not care if those are different I go as a duplicate based on name, address and vehicle. So how would this code work or how would I remove the duplicates as well include those other three columns?

---

BEGIN TRAN


; WITH rankedNamesTest AS (
	SELECT *
		 , ROW_NUMBER() OVER (
			PARTITION BY FIRST, LAST, ADDRESS, CITY, STATE, ZIP, YEAR, MAKE, MODEL
			ORDER BY CUSTID
			)
			AS [RowID]
	FROM
		NamesTest
	)
DELETE r
FROM rankedNamesTest AS r
WHERE r.[RowID] > 1
	
SELECT @@ROWCOUNT AS [Dups_Deleted]

ROLLBACK

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.