删除重复的记录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 recognizeWITH
andOVER
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屋!