<cfquery name="get" datasource="tableOne">
select distinct reportId, Id from
testReports where date = {ts '2004-02-11 00:00:00'}
order by reportId
</cfquery>
<cfloop query="get">
<cfif get.currentRow mod 2>
<cfquery name="delete" datasource="tableOne">
delete from testReports
where Id = #get.Id#
</cfquery>
</cfif>
</cfloop>
-----Original Message-----
From: Plunkett, Matt [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 4:23 PM
To: CF-Talk
Subject: RE: sql question
You could write a stored procedure that does something like:
1. select all rows, order by the column with dupes 2.loop through the rows,
record the column's value at each iteration 3. if the current value is equal
to the last value, delete the current row.
Of course this will leave only one of each duplicate and you asked to only
have one of each duplicate removed. If you really want that, which I doubt,
just modify the proc to do:
1. select all rows, order by columns with dupes 2. loop through the rows,
record the column's value at each iteration.
3. if the current value is equal to the last value, set a flag to indicate
you're in a duplicate 4. when the current value is not equal to the last
value and your flag is on, delete the last value's row
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 4:01 PM
To: CF-Talk
Subject: ot: sql question
hi there.
say I have a database, mssql.
and I know 1 column that should be unique, and actually I have duplicates in
there.
is there a query that I can write that will select them all, find the
duplicates and remove only 1 of the duplicate records?
thanks.
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
