By fabrik


2010-05-04 06:20:32 8 Comments

I have two tables, one for job deadlines, one for describe a job. Each job can take a status and some statuses means the jobs' deadlines must be deleted from the other table.

I can easily SELECT the jobs/deadlines that meets my criteria with a LEFT JOIN:

SELECT * FROM `deadline`
LEFT JOIN `job` ON deadline.job_id = job.job_id
WHERE `status` = 'szamlazva'
OR `status` = 'szamlazhato'
OR `status` = 'fizetve'
OR `status` = 'szallitva'
OR `status` = 'storno'

(status belongs to job table not deadline)

But when I'd like to delete these rows from deadline, MySQL throws an error. My query is:

DELETE FROM `deadline`
LEFT JOIN `job`
ON deadline.job_id = job.job_id
WHERE `status` = 'szamlazva'
OR `status` = 'szamlazhato'
OR `status` = 'fizetve'
OR `status` = 'szallitva'
OR `status` = 'storno'

MySQL error says nothing:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN job ON deadline.job_id = job.job_id WHERE status = 'szaml' at line 1

How can I turn my SELECT into a working DELETE query?

5 comments

@Tahir 2019-02-19 06:57:20

MySQL allows you to use the INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table.

For example, to delete rows from both T1 and T2 tables that meet a specified condition, you use the following statement:

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

Notice that you put table names T1 and T2 between the DELETE and FROM keywords. If you omit T1 table, the DELETE statement only deletes rows in T2 table. Similarly, if you omitT2 table, the DELETE statement will delete only rows in T1 table.

Hope this help.

@Zon 2018-07-15 12:12:56

Try this:

DELETE `deadline`
FROM `deadline`
INNER JOIN `job` ON `deadline`.`job_id` = `job`.`id`
WHERE `job`.`id` = 123

@Roman Losev 2013-05-16 12:05:01

If you are using "table as", then specify it to delete.

In the example i delete all table_1 rows which are do not exists in table_2.

DELETE t1 FROM `table_1` t1 LEFT JOIN `table_2` t2 ON t1.`id` = t2.`id` WHERE t2.`id` IS NULL

@vmanta 2015-02-17 16:11:57

@Roman Losev - What's the point of WHERE 1

@Roman Losev 2015-02-18 11:22:24

It's my default select method... Sorry, for that. Forgot to delete. Edited post.

@Daniel Vassallo 2010-05-04 06:26:51

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` ....

@Urs 2013-08-26 18:19:08

With "AS" I had to use the alias in my clause to make it work for my purpose (delete orphans): DELETE t1 FROM table1 AS t1 LEFT JOIN t2 AS t2 ON t1.uid = t2.result WHERE t2.result IS NULL

@Slawa 2013-10-24 13:27:31

@Ben Dol 2016-02-04 08:24:33

This doesnt work

@Francisco Soto 2010-05-04 06:24:40

DELETE FROM deadline where ID IN (
    SELECT d.ID FROM `deadline` d LEFT JOIN `job` ON deadline.job_id = job.job_id WHERE `status` =  'szamlazva' OR `status` = 'szamlazhato' OR `status` = 'fizetve' OR `status` = 'szallitva' OR `status` = 'storno');

I am not sure if that kind of sub query works in MySQL, but try it. I am assuming you have an ID column in your deadline table.

@Gordon Freeman 2013-01-07 16:44:54

OP still needs to specify what to DELETE to make the query unambiguous. Using In with subqueries makes everything a lot slower. It's best avoided.

@Istiaque Ahmed 2017-11-09 10:31:14

No table mentioned between DELETE and FROM .

Related Questions

Sponsored Content

34 Answered Questions

[SOLVED] Reference - What does this error mean in PHP?

12 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

7 Answered Questions

[SOLVED] Adding multiple columns AFTER a specific column in MySQL

  • 2013-07-09 06:22:00
  • Koala
  • 487862 View
  • 308 Score
  • 7 Answer
  • Tags:   mysql ddl

3 Answered Questions

10 Answered Questions

[SOLVED] How to remove constraints from my MySQL table?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

2 Answered Questions

[SOLVED] Cast from VARCHAR to INT - MySQL

  • 2012-08-26 01:26:30
  • Lenin Raj Rajasekaran
  • 537959 View
  • 227 Score
  • 2 Answer
  • Tags:   mysql sql

6 Answered Questions

[SOLVED] How to delete from multiple tables in MySQL?

5 Answered Questions

[SOLVED] UPDATE multiple tables in MySQL using LEFT JOIN

  • 2009-04-30 13:25:00
  • Paul Oyster
  • 226154 View
  • 157 Score
  • 5 Answer
  • Tags:   mysql sql-update

Sponsored Content