Thank you Razzak! -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. Razzak Memon Sent: Saturday, December 18, 2004 10:50 PM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: Duplicate row identification
At 08:43 PM 12/18/2004 -0600, Claudine Robbins wrote: >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, Create a VIEW which holds both columns/values at any given time! Here's how: Based on TransDetail table in ConComp sample database: CONNECT ConComp IDENTIFIED BY NONE SET ERROR MESSAGE 677 OFF DROP VIEW `TotalItemsInOrder` SET ERROR MESSAGE 677 ON CREATE VIEW `TotalItemsInOrder` (TransID, TotalItems) + AS SELECT TransID, COUNT(*) + FROM TransDetail GROUP BY TransID COMMENT ON VIEW TotalItemsInOrder IS 'Total Items by Order' RETURN TransID is the actual order number and TotalItems is the total count based on number of line items per order. That technique will provide you with a VIEW to hold both column values as well as the count. Hope that helps! Very Best R:egards, Razzak.
