I have the need to mark records in a table as dupplicated via SQL

I came up with the below SQL which just tells me HOW many dupplicated
records I have in the table!(:

SELECT MIN(field0), MIN(field1), Cnt(*) FROM mytable
GROUP BY field0, field1 HAVING Count(field0) > 1
AND Count(field1) > 1

what I envision is that I should somehow use this query as a sub-query
to another one which SETs mytable.dupplicated as .T., but how?
--
Vu Nguyen

// http://www.*-*-*.com/


///////////////////////////////////////////////////////////////
//Disclaimers:  my opinions do not reflect that
//of my employer, USRobotics

You can do this with a subquery, as you suggested.

update mytable
set duplicated = t
where (key) in (
        select (key)
        from mytable
        group by key
        having count(*) > 1)

The tricky bit is that you appear to have a multipart primary key,
field0 and field1. In order to update the table in this way, you must
substitute a concatenated or hashed value of the two (or n) columns
for the comparison, e.g. field0 concat field1. If the fields are
numeric, you may need to cast them as strings, or come up with a
hashing formula to create unique values for the composite.

Ugleee SQL. ;-) At least you don't have a correlated subquery - should
be fairly fast, if you don't have a high percentage of duplicates.

Next question: why isn't there a unique index on the key columns
before insertion, to reject duplicates?