Worked like a charm. Thanks Buddy :-)
_____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Walker, Buddy Sent: Saturday, December 18, 2004 9:29 PM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: Duplicate row identification Claudine I would do this with a declare cursor. DROP CURSOR c1 DECLARE c1 CURSOR for SELECT DISTINCT(field1) from detailTable. OPEN c1 FETCH c1 INTO Vfield1 INDIC IVfield1 SET VAR VRows INTEGER SET VAR vfield1 INTEGER WHILE SQLCODE <> 100 THEN SELECT COUNT(*) INTO VRows INDIC IVRows FROM detailTable WHERE + field1 = .Vfield1 UPDATE thisTable SET field2 = .VRows WHERE field1 = .Vfield1 FETCH c1 INTO VField1 INDIC IVfield1 ENDWHILE DROP CURSOR c1 Buddy -----Original Message----- From: [EMAIL PROTECTED] on behalf of Claudine Robbins Sent: Sat 12/18/2004 9:43 PM To: RBG7-L Mailing List Cc: Subject: [RBG7-L] - RE: Duplicate row identification Buddy or anyone else, please... While we're on this subject. After I identify that some rows are duplicated (this is OK for me, this is a detail table), I want to count the number of duplicates for each value and put the resulting value in a column in the same table. How would I go about doing this? field1 field2 32531 0 32531 0 32531 0 32531 0 I want to update field2 = 4 Claudine :-) _____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Walker, Buddy Sent: Saturday, December 18, 2004 11:47 AM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: Duplicate row identification Robert You can try.. update thisTable set somecolumn = 'X' where itemno in + (select itemno from thatTable group by itemno having count(*) > 1) Buddy -----Original Message----- From: [EMAIL PROTECTED] on behalf of Robert Vincent Sent: Sat 12/18/2004 9:18 AM To: RBG7-L Mailing List Cc: Subject: [RBG7-L] - Duplicate row identification R:Greetings, I seem to recall an SQL syntax that would allow me to identify duplicate rows within a column of a single table. I need to flag rows in a table where an item number already exists within that table. Something like: UPDATE {table} SET {column}='X' where itemno in + (select itemno from {table}) Of course the statement above flags ALL rows, but I thought there was a way to indicate rows that existed more than once. TIA - Rob Vincent
<<attachment: winmail.dat>>
