delete top 2 in Access

Question

What is the correct syntax for this: delete top 2 * FROM table1 in Microsoft Access? I am trying to delete the first 2 rows, and I don't have an id field. Also, I know it's possible to do: select top 2 * FROM table1, so it's hard to think they haven't thought of the same for delete. Thank you!

Otherwise I will just insert top 1 into a temp table, delete all from the original and then reinsert from the temporary. I'm actually interested in preserving one row with a certain condition. But don't understand why I need 3 steps for this when in Sql Server it's much simpler. And if they thought of using top wisely, why did they stop at select and haven't implemented this for the other instructions?

Heinie

Answer 1

The syntax is as follows, I always found it a little strange and was of the same thinking as you:

DELETE FROM (SELECT TOP 2 * FROM Table1)

Analeptic

Answer 2

DELETE * FROM (SELECT TOP 2 * FROM myTableName ORDER BY myTableName.IdNr) AS Deletes;

Tested in Microsoft Access 2016 and working as query or working for Access-VBA

Trahern

So, to delete the last records, could you ORDER BY columnName DESC? – Pavier

Answer 3

The correct syntax is:-

delete top(2) from table1

Gramnegative