sql to CHECK duplicate in field

User avatar
sal21
GoldLounger
Posts: 3326
Joined: 26 Apr 2010, 17:36

sql to CHECK duplicate in field

Post by 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".

User avatar
HansV
Administrator
Posts: 69458
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: sql to CHECK duplicate in field

Post by HansV »

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

User avatar
sal21
GoldLounger
Posts: 3326
Joined: 26 Apr 2010, 17:36

Re: sql to CHECK duplicate in field

Post by sal21 »

HansV wrote:
14 Sep 2020, 13:42
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.

User avatar
HansV
Administrator
Posts: 69458
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: sql to CHECK duplicate in field

Post by 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.
Regards,
Hans

User avatar
sal21
GoldLounger
Posts: 3326
Joined: 26 Apr 2010, 17:36

Re: sql to CHECK duplicate in field

Post by sal21 »

HansV wrote:
14 Sep 2020, 14:02
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