基于另一个表删除表中的所有行

I can't seem to ever remember this query!

我似乎永远都记不住这个查询!

I want to delete all rows in table1 whose ID's are the same as in Table2.

我要删除表1中ID与表2相同的所有行。

So:

所以:

DELETE table1 t1
 WHERE t1.ID = t2.ID

I know I can do a WHERE ID IN (SELECT ID FROM table2) but I want to do this query using a JOIN if possible.

我知道我可以在WHERE ID中输入(从表2中选择ID)，但是如果可能的话，我想使用JOIN来执行这个查询。

14 个解决方案

#1

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

#2

DELETE t1 
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID;

I always use the alias in the delete statement as it prevents the accidental

我总是在delete语句中使用别名，因为它可以防止意外。

DELETE Table1 

caused when failing to highlight the whole query before running it.

在运行查询之前未能突出显示整个查询时导致。

#3

There is no solution in ANSI SQL to use joins in deletes, AFAIK.

在ANSI SQL中没有解决方案，可以使用连接删除，AFAIK。

DELETE FROM Table1
WHERE Table1.id IN (SELECT Table2.id FROM Table2)

Later edit

后编辑

Other solution (sometimes performing faster):

其他解决方案(有时执行速度更快):

DELETE FROM Table1
WHERE EXISTS( SELECT 1 FROM Table2 Where Table1.id = Table2.id)

#4

PostgreSQL implementation would be:

PostgreSQL实现将:

DELETE FROM t1
USING t2
WHERE t1.id = t2.id;

#5

Try this:

试试这个:

DELETE Table1
FROM Table1 t1, Table2 t2
WHERE t1.ID = t2.ID;

or

或

DELETE Table1
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ID = t2.ID;

#6

I think that you might get a little more performance if you tried this

我认为如果你尝试一下，你可能会有更多的表现

DELETE FROM Table1
WHERE EXISTS (
  SELECT 1
  FROM Table2
  WHERE Table1.ID = Table2.ID
)

#7

Found this link useful

发现这个链接有用

Copied from there

从那里复制的

Oftentimes, one wants to delete some records from a table based on criteria in another table. How do you delete from one of those tables without removing the records in both table?

通常，我们希望根据另一个表中的条件从表中删除一些记录。如何删除其中一个表而不删除两个表中的记录?

DELETE DeletingFromTable
     FROM DeletingFromTable INNER JOIN CriteriaTable
     ON DeletingFromTable.field_id = CriteriaTable.id
     WHERE CriteriaTable.criteria = "value";

The key is that you specify the name of the table to be deleted from as the SELECT. So, the JOIN and WHERE do the selection and limiting, while the DELETE does the deleting. You're not limited to just one table, though. If you have a many-to-many relationship (for instance, Magazines and Subscribers, joined by a Subscription) and you're removing a Subscriber, you need to remove any potential records from the join model as well.

关键是指定要从其中删除的表的名称作为SELECT。那么，JOIN和WHERE做选择和限制，而DELETE做删除。不过，您并不局限于一个表。如果您有一个多对多的关系(例如，杂志和订阅者通过订阅连接)，并且您正在删除一个订阅者，那么您还需要从联接模型中删除任何潜在的记录。

 DELETE subscribers, subscriptions
     FROM subscribers INNER JOIN subscriptions 
       ON subscribers.id = subscriptions.subscriber_id
     INNER JOIN magazines 
       ON subscriptions.magazine_id = magazines.id
     WHERE subscribers.name='Wes';

Deleting records with a join could also be done with a LEFT JOIN and a WHERE to see if the joined table was NULL, so that you could remove records in one table that didn't have a match (like in preparation for adding a relationship.) Example post to come.

使用连接删除记录也可以使用左连接和WHERE来查看连接表是否为NULL，这样您就可以删除一个没有匹配的表中的记录(比如准备添加关系)。示例文章。

#8

Since the OP does not ask for a specific DB, better use a standard compliant statement. Only MERGE is in SQL standard for deleting (or updating) rows while joining something on target table.

由于OP不要求特定的DB，所以最好使用符合标准的语句。在SQL标准中，只有MERGE才能在合并目标表上的内容时删除(或更新)行。

merge table1 t1
    using (
        select t2.ID
            from table2 t2
    ) as d
    on t1.ID = d.ID
    when matched then delete;

MERGE has a stricter semantic, protecting from some error cases which may go unnoticed with DELETE ... FROM. It enforces 'uniqueness' of match : if many rows in the source (the statement inside using) match the same row in the target, the merge must be canceled and an error must be raised by the SQL engine.

MERGE具有更严格的语义，避免了一些在删除时可能被忽略的错误。从。它强制执行匹配的“惟一性”:如果源(使用中的语句)中的许多行与目标中的同一行匹配，那么必须取消合并，并且SQL引擎必须引发错误。

#9

This will delete all rows in Table1 that match the criteria:

这将删除表1中符合条件的所有行:

DELETE Table1 
FROM Table2 
WHERE Table1.JoinColumn = Table2.JoinColumn And Table1.SomeStuff = 'SomeStuff'

#10

Referencing MSDN T-SQL DELETE (Example D):

引用MSDN T-SQL删除(示例D):

DELETE FROM Table1
FROM Tabel1 t1
   INNER JOIN Table2 t2 on t1.ID = t2.ID

#11

To Delete table records based on another table

删除基于另一个表的表记录

     Delete From Table1 a,Table2 b where a.id=b.id

    Or

      DELETE FROM Table1
    WHERE Table1.id IN (SELECT Table2.id FROM Table2)

  Or

        DELETE Table1
     FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ID = t2.ID;

#12

This is old I know, but just a pointer to anyone using this ass a reference. I have just tried this and if you are using Oracle, JOIN does not work in DELETE statements. You get a the following message:

这是旧的，我知道，但只是一个指向任何使用这个屁股的人的指针。我刚刚尝试过这种方法，如果您正在使用Oracle，那么在DELETE语句中，JOIN是不起作用的。你会得到以下信息:

ORA-00933: SQL command not properly ended.

ORA-00933: SQL命令未正确结束。

#13

While the OP doesn't want to use an 'in' statement, in reply to Ankur Gupta, this was the easiest way I found to delete the records in one table which didn't exist in another table, in a one to many relationship:

虽然OP不希望使用“in”语句，但在回复Ankur Gupta时，我发现这是最简单的方法来删除一个表中的记录，而这个表在另一个表中并不存在，而是一个到多个关系:

DELETE
FROM Table1 as t1
WHERE ID_Number NOT IN
(SELECT ID_Number FROM Table2 as t2)

Worked like a charm in Access 2016, for me.

对我来说，在2016年的Access中，我就像一个迷人的人。

#14

I often do things like the following made-up example. (This example is from Informix SE running on Linux.)

我经常做以下虚构的例子。(这个例子来自于在Linux上运行的Informix SE)。

The point of of this example is to delete all real estate exemption/abatement transaction records -- because the abatement application has a bug -- based on information in the real_estate table.

本例的要点是基于real_estate表中的信息删除所有房地产豁免/减免交易记录(因为减免应用程序有一个错误)。

In this case last_update != nullmeans the account is not closed, and res_exempt != 'p' means the accounts are not personal property (commercial equipment/furnishings).

在这种情况下，last_update != nullmeans the account is not closed, res_豁免权!= 'p' means the accounts not personal property (commercial equipment/furnishings)。

delete from trans 
where   yr = '16'
and     tran_date = '01/22/2016'
and     acct_type = 'r'
and     tran_type = 'a'
and     bill_no in
(select acct_no from real_estate where last_update is not null
 and res_exempt != 'p');

I like this method, because the filtering criteria -- at least for me -- is easier to read while creating the query, and to understand many months from now when I'm looking at it and wondering what I was thinking.

我喜欢这种方法，因为过滤条件——至少对我来说——在创建查询时更容易阅读，而且当我查看它并想知道我在想什么时，更容易理解它。