Actually I wanted to update
data based on the ID/email.
I ended up with this script:
<cfquery name="DuplikateSuchen" datasource="user" dbtype="ODBC">
SELECT email,ID,suppressionzaehler FROM usertable
</cfquery>
<CFSET zaehler=1>
<CFSET liste_duplikate = "">
<CFLOOP QUERY="DuplikateSuchen" STARTROW="1">
<CFSET aktuelle_email=trim("#email#")>
<CFSET zaehler=zaehler+1>
<CFSET zaehler_innen=zaehler>
<CFLOOP QUERY="DuplikateSuchen" STARTROW="#zaehler#">
<CFIF trim(email) is #aktuelle_email#>
<CFSET liste_duplikate=ListAppend(liste_duplikate,"#id#")>
</CFIF>
<CFSET zaehler_innen=zaehler_innen+1>
</CFLOOP>
<CFIF ListFind(liste_duplikate, trim(#DuplikateSuchen.id#))>
<cfquery name="DuplikateSuppressionzaehlerUpdate" datasource="user" dbtype="ODBC">
update usertable
set suppressionzaehler = 2
where id = #DuplikateSuchen.id#
</cfquery>
</cfif>
</CFLOOP>
Thanks for the people who answered.
Uwe
Hello Jeremy,
Sunday, March 16, 2003, 1:25:48 PM, you wrote:
JH> How about trying the MIN or MAX functions, which will give you the first or
JH> last ids
JH> eg
JH> SELECT u.Email,
JH> MIN(u.id)
JH> FROM user u
JH> JOIN
JH> (
JH> SELECT email FROM user
JH> GROUP BY Email
JH> HAVING Count(*) > 1
JH> ) As u1 ON u.Email = u1.Email
JH> GROUP BY u.Email
JH> Jeremy
>> -----Original Message-----
>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>> Sent: 16 March 2003 00:19
>> To: CF-Talk
>> Subject: Re[2]: SQL first row of duplicates problem
>>
>>
>> Hello Michael,
>> thanks. Takes me
>> in the right direction.
>>
>> I guess this takes THE First record
>> of a query.
>> But I would like to get EVERY First.
>> Is there such a function ?
>>
>> Uwe
>>
>> Saturday, March 15, 2003, 11:36:20 PM, you wrote:
>>
>> MTT> SELECT TOP 1
>>
>>
>> MTT> ----- Original Message -----
>> MTT> From: <[EMAIL PROTECTED]>
>> MTT> To: "CF-Talk" <[EMAIL PROTECTED]>
>> MTT> Sent: Saturday, March 15, 2003 4:02 PM
>> MTT> Subject: OT: SQL first row of duplicates problem
>>
>>
>> >> Hi list,
>> >> I have an SQL-problem
>> >>
>> >> 29 [EMAIL PROTECTED] Mister Mayer 2
>> >> 26 [EMAIL PROTECTED] Mister Mayer 2
>> >> 4 [EMAIL PROTECTED] Misses Miller 2
>> >>
>> >>
>> >> I want to mark every first (but only the first !) line of the
>> >> lines where the email adress is equal.
>> >> e.g. with ID = 29 (see above)
>> >>
>> >> How can I do this ?
>> >>
>> >> SELECT email FROM user
>> >> GROUP BY Email
>> >> HAVING Count(*) > 1
>> >>
>> >> Gives me the email-record but
>> >> I am not sure how to mark the FIRST
>> >> duplicate row.
>> >>
>> >> Ideas ? Thanks !
>> >>
>> >> Uwe
>> >>
>> >>
>> MTT>
>>
JH>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
