删除重复的记录SQL命令错误，VB.net，MS Access [英] Delete Duplicate records SQL Command Error, VB.net, MS Access

问题描述

大家好，

我正在VB.NET 2010中使用SQL Server 2008数据库的应用程序中工作.我现在将SQL数据库转换为MS-Access.现在我在我的SQL查询中出现错误，我不知道Ms Access to Delete Duplicate records中查询的结构.

这是我在Sql数据库中删除重复项的工作代码:

Hi everyone,

I''m working in a application with SQL Server 2008 Database in VB.NET 2010. I converted my SQL database now to MS-Access. Now i got an error in my sql query, i don''t know how the structure of query in Ms Access to Delete Duplicate records.

Here''s my working code in Sql Database to delete duplicate:

Connect()
        SQLQuery = "WITH DuplicateRec AS (SELECT *,row_number() OVER(PARTITION BY M_ID,name,contact ORDER BY M_ID) AS RowNum FROM tblContact) DELETE FROM DuplicateRec WHERE RowNum > 1"
        cmd = New SqlCommand(SQLQuery, conn)
        cmd.ExecuteNonQuery()
        conn.Close()

'When i move to Ms Access i got an error:

Connect()
        SQLQuery = "WITH DuplicateRec AS (SELECT *,row_number() OVER(PARTITION BY M_ID,name,contact ORDER BY M_ID) AS RowNum FROM tblContact) DELETE FROM DuplicateRec WHERE RowNum >1"
        cmd = New OleDbCommand(SQLQuery, conn)
        cmd.ExecuteNonQuery()
        conn.Close()

错误:
无效的SQL语句；预期的删除"，插入"，过程"，选择"或更新".

请帮助我的代码.谢谢

Error:
Invalid SQL statement; expected ''DELETE'', ''INSERT'', ''PROCEDURE'', ''SELECT'', or ''UPDATE''.

Pls help in my code.Thanks

推荐答案

MS Access数据库引擎(JET)无法识别WITH和OVER命令.

要在表中查找重复项，请使用类似以下内容的内容:

MS Access database engine (JET) does not recognize WITH and OVER commands.

To find duplicates in your table, use something like this:

SELECT [FieldWithDuplicates] AS [Value], COUNT([FieldWithDuplicates]) AS [CountOfDuplicates]
FROM YourTable
GROUP BY [FieldWithDuplicates]
HAVING COUNT([FieldWithDuplicates])>=1

要查找非重复项，可以使用 LAST [第一个 [^ ]或 DISTINCT [

To find non-duplicates, you can use LAST[^] or FIRST[^] or DISTINCT[^] functions too, for example:

SELECT DISTINT [FieldWithDuplicates]
FROM YourTable

要删除重复项并保留最新值，请使用:

To delete duplicates and leave the newest values, use:

DELETE *
FROM YourTable
WHERE [ID] NOT IN (SELECT LAST([ID]) FROM YourTable)

这篇关于删除重复的记录SQL命令错误，VB.net，MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！