Delete the dupliacte using TOP in MYSQL query throws Error Code

Question

I just used TOP syntax to delete the duplicate value from table. But it returns error code.

Query:

DELETE top(SELECT COUNT(*)-1 FROM tests WHERE test_name='WALLET_01' AND product_id=25)FROM tests WHERE test_name='WALLET_01' And product_id=25;

Error Code:

Error Code: 1064 You have an error in your SQL syntax;

Executed alone below query return int value as 1:

SELECT COUNT(*)-1 FROM tests WHERE test_name='WALLET_01' AND product_id=25;

Anybody can help me Why I'm getting syntax error?

Possible duplicate : https://stackoverflow.com/questions/8955897/how-to-delete-the-top-1000-rows-from-a-table-using-sql-server-2008 — krithikaGopalakrishnan, Sep 20 '18 at 07:29
@arrchananMohan What dbms are you really using ? your original question is MSSQL but you modify to mysql. — D-Shih, Sep 20 '18 at 08:11
In MySql There is no concept like Top . Alternatively it has limit concept. — Ranjith, Sep 20 '18 at 10:07
Do u have any idea how can I delete the duplicate record except the actual one? — ArrchanaMohan, Sep 20 '18 at 10:17
If you want to delete duplica row，the best thing is your table contain pk if your table didn't have it . You can try my answer. — D-Shih, Sep 20 '18 at 10:25
D-shih I tried your solution. It works like charm. Is there any way to use that in java code base? call it from jdbc? Your solution is working. Just curious to know how can we integrate this into JDBC — ArrchanaMohan, Sep 20 '18 at 10:28

D-Shih · Accepted Answer · 2018-09-20T08:34:46.670

You could not use a subquery in Top

Top syntax

[   
    TOP (expression) [PERCENT]  
    [ WITH TIES ]  
]

expression

Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

We can see it didn't support subquery in TOP only expression

EDIT

I saw you change your use dbms.

Mysql didn't support TOP but you can use LIMIT get the limit row.

If there isn't PK in your table, you can try to use dynamic SQL get to decide how many rows you want to delete in the runtime.

Schema (MySQL v5.6)

CREATE TABLE tests(
test_name VARCHAR(50),
product_id int
);


INSERT INTO tests VALUES('WALLET_01',25);
INSERT INTO tests VALUES('WALLET_01',25);



SET @sql = NULL;
SET @Rn = NULL;
SELECT
   (COUNT(*)-1) 
INTO @Rn
FROM tests
WHERE test_name='WALLET_01' AND product_id=25;


SET @sql = CONCAT('DELETE FROM tests WHERE test_name=''WALLET_01'' AND product_id=25 limit ',@Rn);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Query #1

SELECT * FROM tests;

| test_name | product_id |
| --------- | ---------- |
| WALLET_01 | 25         |

View on DB Fiddle

Also, Is there anyway to delete old record to and keep the one which is added last. There is a column called s.no . I just want to keep the record which has latest s.no. Like s.no 1, 2 both are duplicate (test_name, product_id) just want to delete s.no 1 and keep 2 as it's — ArrchanaMohan, Sep 20 '18 at 10:33
How do you know which row is the newest ? Because there isn't pk or date column — D-Shih, Sep 20 '18 at 11:30

Delete the dupliacte using TOP in MYSQL query throws Error Code

1 Answers1