Syntax error in DELETE FROM (SELECT)

Question

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.

Lajos Arpad · Accepted Answer · 2018-01-19 12:11:46Z

1

This is how you remove the first found row:

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

answered Jan 19, 2018 at 12:11

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error in DELETE FROM (SELECT)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related