Delete statement in a same table

Question

I need to query a delete statement for the same table based on column conditions from the same table for a correlated subquery.

I can't directly run a delete statement and check a condition for the same table in mysql for a correlated subquery.

I want to know whether using temp table will affect mysql's memory/performance?

Any help will be highly appreciated.

Thanks.

Answer 1

You can make mysql do the temp table for you by wrapping your "where" query as an inline from table.

This original query will give you the dreaded "You can't specify target table for update in FROM clause":

DELETE FROM sametable
WHERE id IN (
    SELECT id FROM sametable WHERE stuff=true
)

Rewriting it to use inline temp becomes...

DELETE FROM sametable
WHERE id IN (
 SELECT implicitTemp.id from (SELECT id FROM sametable WHERE stuff=true) implicitTemp
)

Answer 2

Your question is really not clear, but I would guess you have a correlated subquery and you're having trouble doing a SELECT from the same table that is locked by the DELETE. For instance to delete all but the most recent revision of a document:

DELETE FROM document_revisions d1 WHERE edit_date < 
  (SELECT MAX(edit_date) FROM document_revisions d2 
   WHERE d2.document_id = d1.document_id);

This is a problem for MySQL.

Many examples of these types of problems can be solved using MySQL multi-table delete syntax:

DELETE d1 FROM document_revisions d1 JOIN document_revisions d2 
  ON d1.document_id = d2.document_id AND d1.edit_date < d2.edit_date;

But these solutions are best designed on a case-by-case basis, so if you edit your question and be more specific about the problem you're trying to solve, perhaps we can help you.

In other cases you may be right, using a temp table is the simplest solution.

Answer 3

can't directly run a delete statement and check a condition for the same table

Sure you can. If you want to delete from table1 while checking the condition that col1 = 'somevalue', you could do this:

DELETE
  FROM table1
 WHERE col1 = 'somevalue'

EDIT
To delete using a correlated subquery, please see the following example:

create table project (id int);
create table emp_project (id int, project_id int);

insert into project values (1);
insert into project values (2);
insert into emp_project values (100, 1);
insert into emp_project values (200, 1);

/* Delete any project record that doesn't have associated emp_project records */
DELETE
  FROM project
 WHERE NOT EXISTS
     (SELECT *
        FROM emp_project e
       WHERE e.project_id = project.id);


/* project 2 doesn't have any emp_project records, so it was deleted, now
   we have 1 project record remaining */
SELECT * FROM project;

Result:
id
1

Answer 4

Create a temp table with the values you want to delete, then join it to the table while deleting. In this example I have a table "Games" with an ID column. I will delete ids greater than 3. I will gather the targets in a temp table first so I can report on them later.

DECLARE @DeletedRows TABLE (ID int)

insert
    @DeletedRows
        (ID)
select
    ID
from
    Games
where
    ID > 3


DELETE
    Games
from
    Games g
join
    @DeletedRows x
    on x.ID = g.ID  

Answer 5

I have used group by aggregate with having clause and same table, where the query was like

DELETE 
FROM TableName
WHERE id in 
   (select implicitTable.id 
    FROM (
          SELECT id 
          FROM `TableName` 
          GROUP by id 
          HAVING count(id)>1
          ) as implicitTable
   )

Answer 6

You can delete from same table. Delete statement is as follows

DELETE FROM table_name
WHERE some_column=some_value

Answer 7

You mean something like:

DELETE FROM table WHERE someColumn = "someValue";

?

This is definitely possible, read about the DELETE syntax in the reference manual.