Delete ... from ... Where ... In

DELETE ... FROM ... WHERE ... IN

You can achieve this using exists:

DELETE
  FROM table1
 WHERE exists(
           SELECT 1
             FROM table2
            WHERE table2.stn = table1.stn
              and table2.jaar = year(table1.datum)
       )

How to delete from a table where ID is in a list of IDs?

Your question almost spells the SQL for this:

DELETE FROM table WHERE id IN (1, 4, 6, 7)

How to Delete Records NOT IN

I would like to start with assumptions.

1. You have a chainlike data model:
   Projects --* ProjectSchemes --* Schemes
2. Your target is to have only valid chains, so no ProjectSchemes without Project, no Schemes without ProjectSchemes.
3. NULL is not a valid value for one of your ids.
4. All ids are unique in their table
5. You don't use referential integrity mechanisms of your database

As a result your SELECT would list the scheme_id for all Schemes in the Schemes table.

Said that, you should start to delete all ProjectSchemes without a corresponding Project. These are ProjectSchemes with an id of NULL or an id which does not exists in the Projects Table:

DELETE ProjectSchemes WHERE (Project_Id is NULL) OR 
(NOT EXISTS (SELECT * FROM Projects WHERE
             Projects.Project_Id = ProjectSchemes.Project_Id))

After deleting the ProjectsSchemes without a Project we now may have some new orphans in the Schemes Table. The next thing is now to delete all Schemes which have an id of NULL or an id which does not exists in the ProjectsSchemes Table:

DELETE Schemes WHERE (Scheme_Id is NULL) OR 
(NOT EXISTS (SELECT * FROM ProjectSchemes WHERE
             ProjectSchemes.Scheme_Id = Schemes.Scheme_Id))

There is still a chance to have schemes which are not connected to a project without deleting the ProjectSchemes.

Delete all rows in a table based on another table

DELETE Table1
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID

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'));

mySQL: How to Delete in WHERE IN clause

MySQL is generally unhappy if you try to SELECT and DELETE from the same table in the same query -- even if the SELECT is in a subquery.

But you can do more joins in your DELETE query.

DELETE a, ai
FROM album a
LEFT JOIN album_image ai ON ai.album_id = a.album_id
LEFT JOIN album p ON a.parent_album_id = p.album_id
WHERE a.album_id = 76 OR p.album_id = 76

PHP MYSQL DELETE FROM WHERE AND clause

Only 1 WHERE needed, try this:

DELETE FROM content WHERE note_id = '7' AND userid = '1'
DELETE FROM note WHERE id = '7' AND userid = '1'

DELETE * FROM TABLE WHERE this=that not working

You don't need the * when using DELETE. Just do

"DELETE FROM users WHERE cookie='$cookie'"

why my `delete where id in` statement drop all records in the table?

...outer sql select id from () tmp selects a non exsit field id
which lead to an error, but mysql execute this sql and delete all
records.

I want to konw why this happened.

This subquery will not run on its own:

select id from (
    select min(id) from reply group by ex_type and ex_id having count(1) > 1
) tmp
/* SQL Error (1054): Unknown column 'id' in 'field list' */

But when it is run inside a subquery then according to scope resolution rules the id column resolves to the id column of the outer query since the requested column is not present in the FROM clause. The query is essentially like:

delete from reply where id in (
    select reply.id from (
        select min(id) from reply group by ex_type and ex_id having count(1) > 1
    ) tmp
)
/* Affected rows: 4  Found rows: 0  Warnings: 0  Duration for 1 query: 0.031 sec. */

The condition is true for all rows since 1 IN (1), 2 IN (2), 3 IN (3)... are all true. Fixing the typo (group by ex_type and ex_id) will not fix the problem, change your query to this:

delete from reply where id in (
    select tmp.id from (
        select min(id) as id from reply group by ex_type, ex_id having count(1) > 1
    ) tmp
)

---

Related Topics