How to perform a delete with in (select)?

Question

How to perform a delete with in (select)?

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I have the following query

DELETE FROM abono where id='dos' in (select * FROM abono WHERE id='dos' limit 2,1)

but my mysql tells me

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

my table is called a subscription

id    | monto
'uno' | 5.00
'uno' | 3.79
'dos' | 4.00
'tres'| 1.00
'dos' | 2.90
'uno' | 6.00
'dos' | 7.50
'tres'| 9.00

As it does not have primary key I use this query to tell it to index delete

select * FROM abono WHERE id='dos' limit 2,1

return me

id | monto
'dos | 7.50

but I do not know how I can delete that data

mysql sql

asked by goku venz 27.03.2018 в 05:18

source

2 answers

How to divide a TableRow into two equal parts? Add the value of a list of dictionaries with the same key

score 2 · Answer 1

As I put you in the comments, the error specified in the message occurs because you use a LIMIT within the subquery of delete . You have to find a way that you do not use LIMIT in the subquery. Nor can the same table of delete be used in the subquery used in the WHERE conditions.

But interestingly, it is not allowed in the direct subquery but in a subquery of a subquery (as you can see in SQL Fiddle ). Then you could do something like this:

DELETE FROM abono WHERE id = 'dos' AND monto = (SELECT monto FROM (SELECT monto FROM abono WHERE id='dos' LIMIT 2,1) AS m)

Here stylized to be read better:

DELETE FROM abono 
WHERE  id = 'dos' 
  AND  monto = (
                SELECT monto FROM (
                                   SELECT monto FROM abono WHERE id='dos' LIMIT 2,1 
                                  ) AS m
               )

Anyway, as I put you in the comments, this solution has a problem: if there are several repeated records with id "two" and amount 7.50, it will delete all of them. My recommendation would be that you look for another option, add a primary key and opt for something simpler and more sustainable.

.

score 0 · Answer 2

0

Usually you can not modify the data of a table using subquery. See the following link

link

One position is to rename the subquery

answered by 27.03.2018 в 07:46