What is the correct syntax for DELETE TOP statement?

Question

I want do delete only 1 row of the table "trader_inventory" where the "item_id" is 14 and "trader_id" is 1. This is the code I've tried:

DELETE TOP 1 
FROM trader_inventory 
WHERE item_id = 14 AND trader_id = 1;

I also tried this to see if its only working with 1 condition:

DELETE TOP 1
FROM trader_inventory
WHERE item_id = 14;

I also tried with brackets at "TOP (1)"

SQL says Syntax-error at '1 FROM trader_inventory WHERE item_id = 14'

I can't explain why it's not working. I am trying to find a solution for 2 hours now. I hope someone can help me. I saw that some people write their table names in []-brackets but that didn't work for me. SQL marked it as wrong.

Answer 1

In MySQL, you would use:

DELETE FROM trader_inventory
    WHERE item_id = 14 AND trader_id = 1
    LIMIT 1;

Here is a db<>fiddle.

There is no SELECT TOP in MySQL. If you were using SQL Server (as suggested by the use of TOP), you can use a subquery (or CTE):

DELETE ti
    FROM (SELECT TOP (1) ti.*
          FROM trader_inventory ti
          WHERE item_id = 14 AND trader_id = 1
         ) ti;

Note: SELECT TOP is usually used with an ORDER BY so the results are consistent. This deletes an arbitrary row. And if you run it on a table with the same rows, different rows could be chosen.