How to Delete from Select in MySQL

How to delete from select in MySQL?

SELECT (sub)queries return result sets. So you need to use IN, not = in your WHERE clause.

Additionally, as shown in this answer you cannot modify the same table from a subquery within the same query. However, you can either SELECT then DELETE in separate queries, or nest another subquery and alias the inner subquery result (looks rather hacky, though):

DELETE FROM posts WHERE id IN (
    SELECT * FROM (
        SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
    ) AS p
)

Or use joins as suggested by Mchl.

How to delete entries from a SELECT query result in mysql?

If we have a complex query that returns the id value of rows in wp_posts that we want to delete (assuming that id is the primary key or a unique key of a row in the table)... as an example

SELECT p.id
  FROM wp_posts p
  JOIN wp_icl_translations t
    ON t.element_id = p.id 
 WHERE t.language_code = 'es-es'
   AND t.element_type  = 'post_product'
   AND p.post_type     = 'product'
 GROUP
    BY p.id
    

We can then use that query as an inline view. We wrap the query in parens and reference it in the FROM clause of another query. MySQL requires that we assign an alias to thhe inline view (or derived table in the MySQL vernacular).

We can join the result from the inline view that back to the table we want to remove rows from. We write this a SELECT statement first

SELECT r.*
  FROM ( -- inline view
         SELECT p.id
           FROM wp_posts p
           JOIN wp_icl_translations t
             ON t.element_id = p.id 
          WHERE t.language_code = 'es-es'
            AND t.element_type  = 'post_product'
            AND p.post_type     = 'product'
          GROUP
             BY p.id
       ) q
  JOIN wp_posts r
    ON r.id = q.id
    

to return the set of rows to be removed. We can verify that this is the intended set, or insert (create table as) the set of rows into backup...

Once we are confident that the SELECT is returning the rows we want to remove, we can convert it into a DELETE statement by replacing the SELECT keyword with DELETE.

DELETE r.*
  FROM ( -- inline view
         SELECT p.id
           FROM wp_posts p
           JOIN wp_icl_translations t
             ON t.element_id = p.id 
          WHERE t.language_code = 'es-es'
            AND t.element_type  = 'post_product'
            AND p.post_type     = 'product'
          GROUP
             BY p.id
       ) q
  JOIN wp_posts r
    ON r.id = q.id

MYSQL - DELETE FROM SELECT

In MySQL you can't delete from the table you are selecting from. Try

DELETE FROM reservation_seats
WHERE id IN ( 
              select * from 
              (
                   SELECT id FROM reservation_seats
                   WHERE reservation_id NOT IN ( SELECT id FROM reservation)
              )
);

to trick MySQL to do it anyway. BTW this could be made simpler like this

DELETE FROM reservation_seats
WHERE reservation_id NOT IN ( SELECT id FROM reservation)

How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?

You need to identify the primary key in TableA in order to delete the correct record. The primary key may be a single column or a combination of several columns that uniquely identifies a row in the table. If there is no primary key, then the ROWID pseudo column may be used as the primary key.

DELETE FROM tableA
WHERE ROWID IN 
  ( SELECT q.ROWID
    FROM tableA q
      INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) OR q.memotext NOT LIKE '%/%/%')
      AND (u.FldFormat = 'Date'));

Delete with select in mysql

You cannot delete from a table and select from that same table in a subselect.

You can however use that table in a self-join.

DELETE t1 FROM test t1
INNER JOIN test t2 ON (t1.id = t2.parent)
WHERE t2.id = 2 and t2.value = 'value'

You cannot use limit not order by in a joined delete statement.

It's either the join or the limit/order by, take your pick.

See: http://dev.mysql.com/doc/refman/5.5/en/delete.html

Select and Delete with a single query

delete from rooms
where room_initiating_user_id in (select user_id from users where user_connected = 0)
  and room_target_user_id in (select user_id from users where user_connected = 0)

Delete selected rows in mysql

you can use this query

delete from items
where order_id in 
   (SELECT order_id
    FROM items
    GROUP BY order_id
    HAVING count(*)  = 1
   )

it will delete the row where order_id exist only once in items table

How to delete in mysql version 5.7

Here's code that works for me.

DELETE table1
FROM table1
INNER JOIN (
 SELECT MAX(id) AS lastId, cl_date
   FROM table1
   WHERE code = "code1" AND closed BETWEEN '2021-03-01' AND '2021-04-30'
  GROUP BY cl_date
 HAVING COUNT(*) > 1) dup ON dup.cl_date = table1.cl_date
WHERE table1.id < dup.lastId

---

Related Topics