Re: HOW TO FIND THE DUPLICATE VALUES IN A VERTICAL TABLE

Here's one method using SQL92:

UPDATE myid_props
  SET duplicate_flag = 'Y'
  WHERE NOT EXISTS
    (SELECT *

      FROM myid_props AS P
      LEFT JOIN myid_props AS Q
        ON P.prop_name = Q.prop_name
          AND P.prop_value = Q.prop_value
          AND P.id > Q.id
      WHERE Q.prop_name IS NULL
        AND P.id = myid_props.id) ;

I don't expect it's the most efficient solution but maybe it will help.

FWIW here are my views on a similar table design posted in another place: http://www.google.com/groups?selm=A2587C8B-CC9A-48B3-86A2-9823445BDF1F%40microsoft.com

--

David Portas
SQL Server MVP
--
Received on Thu Oct 21 2004 - 18:15:31 CDT