blocks|key|3046291|text|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');|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3046292|我不确定这种子查询在MySQL中是否有效，但可以尝试一下。我假设您在deadline表中有一个ID列。|unstyled|3046293|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>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');
</code></pre>

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.

blocks|key|3260077|text|您只需指定要对哪些表应用DELETE即可。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3260078|仅删除deadline行：|3260079|DELETE+`deadline`+FROM+`deadline`+LEFT+JOIN+`job`+....|code-block|syntax|javascript|3260080|删除deadline和job行：|3260081|DELETE+`deadline`,+`job`+FROM+`deadline`+LEFT+JOIN+`job`+....|3260082|仅删除job行：|3260083|DELETE+`job`+FROM+`deadline`+LEFT+JOIN+`job`+....|3260084|entityMap^0|C|6|0|3|8|0|0|2|8|B|3|0|0|3|3|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|13|8|@$9|14|A|15|B|C]|$9|16|A|17|B|C]]|D|@]|E|$]]|$1|O|3|P|5|J|7|18|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|S|3|T|5|J|7|1C|8|@]|D|@]|E|$K|L]]|$1|U|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]]|V|$]]

You simply need to specify on which tables to apply the <code>DELETE</code>. 

Delete only the <code>deadline</code> rows:

<pre><code>DELETE `deadline` FROM `deadline` LEFT JOIN `job` ....
</code></pre>

Delete the <code>deadline</code> and <code>job</code> rows:

<pre><code>DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....
</code></pre>

Delete only the <code>job</code> rows:

<pre><code>DELETE `job` FROM `deadline` LEFT JOIN `job` ....
</code></pre>

blocks|key|306634|text|如果您使用的是"table+as"，则指定要删除的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|306635|在示例中，我删除了table_2中不存在的所有table_1行。|306636|DELETE+t1+FROM+`table_1`+t1+LEFT+JOIN+`table_2`+t2+ON+t1.`id`+=+t2.`id`+WHERE+t2.`id`+IS+NULL|code-block|syntax|javascript|306637|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>DELETE t1 FROM `table_1` t1 LEFT JOIN `table_2` t2 ON t1.`id` = t2.`id` WHERE t2.`id` IS NULL
</code></pre>

blocks|key|3046374|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3046375|DELETE+`deadline`
FROM+`deadline`
INNER+JOIN+`job`+ON+`deadline`.`job_id`+=+`job`.`id`
WHERE+`job`.`id`+=+123|code-block|syntax|javascript|3046376|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>DELETE `deadline`
FROM `deadline`
INNER JOIN `job` ON `deadline`.`job_id` = `job`.`id`
WHERE `job`.`id` = 123
</code></pre>

blocks|key|309737|text|MySQL允许您在DELETE语句中使用INNER+JOIN子句来删除一个表中的行和另一个表中匹配的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|309738|例如，要从满足指定条件的T1和T2表中删除行，请使用以下语句：|309739|DELETE+T1,+T2
FROM+T1
INNER+JOIN+T2+ON+T1.key+=+T2.key
WHERE+condition;|code-block|syntax|javascript|309740|请注意，您将表名T1和T2放在DELETE和FROM关键字之间。如果省略T1表，则DELETE语句仅删除T2表中的行。类似地，如果您删除表，omitT2语句将只删除T1表中的行。|309741|希望这能有所帮助。|309742|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

<pre><code>DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;
</code></pre>

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.

blocks|key|3260176|text|这个脚本对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3260177|DELETE+t
FROM+table+t
INNER+JOIN+join_table+jt+ON+t.fk_column+=+jt.id
WHERE+jt.comdition_column…;|code-block|syntax|javascript|3260178|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This script worked for me:
<pre><code>DELETE t
FROM table t
INNER JOIN join_table jt ON t.fk_column = jt.id
WHERE jt.comdition_column…;
</code></pre>

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 <code>SELECT</code> the jobs/deadlines that meets my criteria with a <code>LEFT JOIN</code>:

<pre><code>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'
</code></pre>

(<code>status</code> belongs to <code>job</code> table not <code>deadline</code>)

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

<pre><code>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'
</code></pre>

MySQL error says nothing:

<blockquote>
 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 <code>job</code> ON deadline.job_id = job.job_id WHERE <code>status</code> = 'szaml' at line 1
</blockquote>

How can I turn my <code>SELECT</code> into a working <code>DELETE</code> query?

Deleting rows with MySQL LEFT JOIN

服务器

MySQL

我有两个表，一个用于工作截止日期，另一个用于描述工作。每个作业都可以有一个状态，有些状态意味着作业的截止日期必须从另一个表中删除。我可以使用LEFT JOIN轻松地SELECT符合我的标准的工作/截止日期SELECT * FROM `deadline`LEFT JOIN `job` ON deadline.job_id...

问使用MySQL左连接删除行
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MySQL左连接删除行EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MySQL左连接删除行
EN