MySQL Delete from With Subquery as Condition

MySQL DELETE FROM with subquery as condition

You cannot specify target table for delete.

A workaround

create table term_hierarchy_backup (tid int(10)); <- check data type

insert into term_hierarchy_backup 
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th1.parent = 1015;

DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (select tid from term_hierarchy_backup);

Cannot delete from same table in subquery from

Try this

DELETE c FROM concorre C 
JOIN (SELECT AVG(B.votoTot) average FROM concorre B WHERE B.dataSer='2014-02-23') d
WHERE C.dataSer='2014-02-24' 
AND C.votoTot < d.average /5 

MySQL: DELETE FROM with information from a subquery

Use the IN clause

DELETE FROM users WHERE id IN (SELECT id FROM delete_requests)

MySql 8 delete subquery with limit

DELETE tableA.*
FROM tableA 
JOIN (SELECT tA.idTableA
      FROM tableB tB
      LEFT JOIN tableA tA ON tB.idTableB = tA.idTableA
      WHERE tB.campoX = 100 LIMIT 1) USING (idTableA);

?

MySQL DELETE FROM with UNION subquery by IN condition

Try this version instead:

DELETE FROM startpoint
    WHERE id IN (select *
                 from ((SELECT id FROM stairs WHERE building = 123)
                       UNION
                      (SELECT id FROM lift WHERE building = 123)
                       UNION
                      (SELECT id FROM qrcodeid WHERE building = 123)
                )

I think the issue is an arcane issue with the definition of a subquery. A subquery is a select statement, whereas a union is a conjunction of select statements.

EDIT:

Actually, if you want efficiency, you wouldn't use this approach at all. I was just trying to show how to fix the error. A better solution would be:

DELETE sp FROM startpoint sp
    WHERE EXISTS (select 1 from stairs s where s.building = 123 and s.id = sp.id) or
          EXISTS (select 1 from lift l where l.building = 123 and l.id = sp.id) or
          EXISTS (select 1 from qrcodeid q where q.building = 123 and q.id = sp.id);

Indexes are recommended on stairs(id, building), lift(id, building), and qrcodeid(id, building).

MySQL delete statement with subquery in IN cluase and LIMIT

You are deleting from a single table, which for some reason MySQL doesn't support table alias' for:-

DELETE 
FROM employees 
WHERE emp_no IN (
    SELECT ee.emp_no
    FROM (
        SELECT e.emp_no
        FROM employees e, salaries s
        WHERE e.emp_no = s.emp_no
        GROUP BY e.emp_no
        HAVING COUNT(s.emp_no) = 1)
    ee) 
AND emp_no NOT IN (
    SELECT dmm.emp_no
    FROM (
        SELECT dm.emp_no
        FROM dept_manager dm)
    dmm)
LIMIT 8384

Delete from table using subquery from another table

You need to find the student in sub-query as follows:

DELETE FROM students
 WHERE id IN 
   (SELECT student_id 
      FROM exam_results 
     WHERE percentage < (select AVG(percentage) from exam_results);

You can also use the analytical function but that depends on your MySql Version. so please tag the database and its version on which you are working.

MySQL DELETE With a Sub-Query using Having and Count

I think you need to use temporary table to achieve your need as below:

1. Step1: Create temp table

   CREATE TEMPORARY TABLE MyTemp
   SELECT guid FROM account 
   GROUP BY guid,type HAVING count(type) > 1;
   

2. Use the temp table in your delete statement

   DELETE FROM account 
   WHERE guid IN (SELECT guid FROM MyTemp);
   

3. Drop the temp table

   DROP TEMPORARY TABLE MyTemp;
   

EDIT: I think a work around with *two nested tables also works:

  DELETE FROM account 
    WHERE guid IN 
     (SELECT guid FROM 
       (SELECT guid FROM account 
       GROUP BY guid,type HAVING count(type) > 1) as MyTemp
    )

---

Related Topics