Mysql, Delete Query with a Join

MYSQL - Delete Query with Join

This should work:

DELETE T
FROM TEST2 T
INNER JOIN TEST1 on TEST1.FIELD2 = T.FIELD2
WHERE TEST1.FIELD1 = 22;

Sample Fiddle Demo

I think you can also do it with IN:

DELETE FROM Test2
WHERE Field2 IN (
    SELECT Field2 
    FROM Test1
    WHERE Field1 = 22)

Delete with Join in MySQL

You just need to specify that you want to delete the entries from the posts table:

DELETE posts
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = :client_id

EDIT: For more information you can see this alternative answer

Delete from one table with join

I am not sure about your requirement.
What I understood from your question is you want to delete all the emails of jobs which are closed.
try this one;

DELETE e FROM emailNotification e 
LEFT JOIN jobs j ON j.jobId = e.jobId 
WHERE j.active = 1 AND CURDATE() < j.closeDate

DELETE with multiple JOIN in MySQL?

Your syntax is wrong.

Use this with aliases also:

DELETE r
FROM rapport r
INNER JOIN course c ON r.course_id = c.id
INNER JOIN reunion u ON c.reunion_id  = u.id
INNER JOIN paris p ON r.paris_id = p.id 
WHERE u.id = 231431 AND p.bookmaker_id = 3

If you want to delete from all tables then you must specify their aliases after DELETE:

DELETE r, c, u, p
FROM rapport r
INNER JOIN course c ON r.course_id = c.id
INNER JOIN reunion u ON c.reunion_id  = u.id
INNER JOIN paris p ON r.paris_id = p.id 
WHERE u.id = 231431 AND p.bookmaker_id = 3

Delete with join on the same table and limit clause

UPDATED based on your comments

DELETE f
  FROM fsession f JOIN
(
  SELECT session_id, subscriber_id
    FROM fsession
   WHERE status IN('Started', 'Finished')
   GROUP BY Session_Id, Subscriber_Id
  HAVING MAX(status = 'Started')  > 0
     AND MAX(status = 'Finished') > 0
  LIMIT 1000 -- limit here means how many pairs of rows, so it's effectively 2x rows
) q ON f.session_id = q.session_id
   AND f.subscriber_id = q.subscriber_id

To know how many rows were deleted use ROW_COUNT()

SELECT ROW_COUNT();

Here is SQLFiddle demo

Deleting rows with MySQL LEFT JOIN

You simply need to specify on which tables to apply the DELETE.

Delete only the deadline rows:

DELETE `deadline` FROM `deadline` LEFT JOIN `job` ....

Delete the deadline and job rows:

DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....

Delete only the job rows:

DELETE `job` FROM `deadline` LEFT JOIN `job` ....

Delete using Inner Joins

What you posted works fine for SQL Server; for MySQL below should do the job

DELETE tableA
FROM tableA
INNER JOIN tableB ON tableB.fieldA = tableA.fieldA;

SQL DELETE with INNER JOIN

Add .* to s in your first line.

Try:

DELETE s.* FROM spawnlist s
INNER JOIN npc n ON s.npc_templateid = n.idTemplate
WHERE (n.type = "monster");

---

Related Topics