August 1, 2003 at 8:33 am
Is there a function or identifier comparable to Oracle's ROW_ID in SQL Server? I am looking for a way to delete the duplicate rows, but keep one.
August 2, 2003 at 12:59 am
No there's no ROW_ID. But, I'd like to see it in the next version.
Is there any column that differentiates between the duplicate rows (an identity column perhaps)? Or, does "duplicate rows" mean exactly that - every column identical?
If every column is identical then I doubt a set-based delete can be done - you'd need to implement a cursor-based delete.
Cheers,
- Mark
Cheers,
- Mark
August 2, 2003 at 9:14 am
You can always brute force by creating a temp table, setting a unique index with ignore dupes set, then appending the table into it. That will definitely give you uniques.
Andy
August 3, 2003 at 8:04 pm
if I unserstood problem correctly this is a another solution..
select distinct * int #temp
from sourceTable
drop table sourcetable
insert sourceTable
select * from #temp
drop table #temp
go
August 3, 2003 at 8:24 pm
Don't remember exactly where I found this, or I would attribute to the original author.
Have used it in a couple of cases to remove dups where there was at least one column I could use to determine which row to delete.
prospect is the name of the table where the row is to be deleted
prospectid is an identity column, I keep the lowest number, no reason for that option
fields in where clause are the key to identifying dup rows.
DELETE from prospect
where exists (
SELECT p2.prospectid
from prospect p2
WHERE p2.ContractNumber=prospect.ContractNumber and
p2.CustCredAct = prospect.CustCredAct and
p2.Lessor = prospect.Lessor and
p2.spec_name = prospect.spec_name and
p2.prospectid < prospect.prospectid )
August 4, 2003 at 12:01 pm
Or, to bum it a little... 🙂
DELETEp1
FROMprospect p1
JOINprospect p2
ON(p1.ContractNumber = p2.ContractNumber
and p1.CustCredAct = p2.CustCredAct and
p1.Lessor = p2.Lessor)
WHEREp1.prospectid > p2.prospectid
Signature is NULL
August 4, 2003 at 1:23 pm
Cool - as I said, I found it elsewhere and used it pretty much as it was. Thanks for the update.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply