1
votes

I need to run a query to delete the top 1 row from an Access database table where the rows are identical.

I'm getting all of the records into a DataTable as follows:

patchDA = New OleDbDataAdapter("SELECT * FROM [Sales Lines] WHERE Order_Number = @Ord AND lineNumber = @LN", con)
patchDA.SelectCommand.Parameters.Add("@Ord", OleDbType.Integer).Value = OrderNum
patchDA.SelectCommand.Parameters.Add("@LN", OleDbType.Integer).Value = i

Dim lineDT As New DataTable
patchDA.Fill(lineDT)

I'm then trying to delete duplicate records (There will only ever be 1 duplicate at most) using this command.

If lineDT.Rows.Count > 1 Then
   Dim lineCmd As New OleDbCommand("DELETE TOP(1) FROM [Sales Lines] WHERE Order_Number = @Ord AND lineNumber = @LN", con)
   patchDA.SelectCommand.Parameters.Add("@Ord", OleDbType.Integer).Value = OrderNum
   patchDA.SelectCommand.Parameters.Add("@LN", OleDbType.Integer).Value = i
   lineCmd.ExecuteNonQuery()

However, an error occurs saying there is a syntax error in the delete statement.

What is the error?

I also tried using

DELETE FROM [Sales Lines] WHERE Order_Number = @Ord AND lineNumber = @LN LIMIT 1

but the same thing happened.

1

1 Answers

1
votes

This is how you remove the first found row:

DELETE FROM (select top 1 * from [Sales Lines] WHERE Order_Number = @Ord AND lineNumber = @LN)