[RBG7-L] - RE: Duplicate row identification

[Walker, Buddy]

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 

<<winmail.dat>>