sql to CHECK duplicate in field

[sal21]

I have a field CODEAN.
with a ADO Sql is possible to check if exists a duplicates in CODAN and if is true fill DUP_CODEAN field with "SI".

[HansV]

Is DUP_CODEAN a field in the table, or is it a field that you want to return in a query (recordset)?

[sal21]

Is DUP_CODEAN a field in the table, or is it a field that you want to return in a query (recordset)?

Field in the same table.

[HansV]

Use this SQL:

Code: Select all

    SQL = "UPDATE [TableName] SET DUP_CODEAN = 'SI' WHERE (SELECT Count(*) " & _
        "FROM [TableName] AS T WHERE T.CODEAN=[TableName].CODEAN)>1"

Change TableName to the name of your table.

[sal21]

Use this SQL:

Code: Select all

    SQL = "UPDATE [TableName] SET DUP_CODEAN = 'SI' WHERE (SELECT Count(*) " & _
        "FROM [TableName] AS T WHERE T.CODEAN=[TableName].CODEAN)>1"

Change TableName to the name of your table.

work!
tKS