다른 테이블을 기반으로 한 테이블의 모든 행 삭제

다른 테이블을 기반으로 한 테이블의 모든 행 삭제

---

이 쿼리를 기억할 수없는 것 같습니다!

ID가 Table2와 동일한 table1의 모든 행을 삭제하고 싶습니다.

그래서:

DELETE table1 t1
 WHERE t1.ID = t2.ID

나는 WHERE ID IN (SELECT ID FROM table2)을 할 수 있다는 것을 알고 있지만 가능한 경우 JOIN을 사용 하여이 쿼리를 수행하고 싶습니다.

---

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

---

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

우발적 인 것을 방지하기 때문에 항상 delete 문에서 별칭을 사용합니다.

DELETE Table1 

전체 쿼리를 실행하기 전에 강조 표시하지 못할 때 발생합니다.

---

ANSI SQL에는 삭제, AFAIK에서 조인을 사용하는 솔루션이 없습니다.

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

나중에 편집

다른 솔루션 (때로는 더 빠르게 수행됨) :

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

---

PostgreSQL 구현은 다음과 같습니다.

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

---

이 시도:

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

또는

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

---

이것을 시도하면 조금 더 성능을 얻을 수 있다고 생각합니다

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

---

그러면 Table1기준과 일치하는 모든 행이 삭제됩니다 .

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

---

이 링크가 유용함

거기에서 복사

종종 다른 테이블의 기준에 따라 테이블에서 일부 레코드를 삭제하려고합니다. 두 테이블의 레코드를 제거하지 않고 이러한 테이블 중 하나에서 어떻게 삭제합니까?

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

핵심은 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.

---

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.

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.

---

Referencing MSDN T-SQL DELETE (Example D):

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

---

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;

---

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:

ORA-00933: SQL command not properly ended.

---

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:

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.

---

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

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.

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

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.

---

delete
    table1
from 
    t2
where
    table1.ID=t2.ID

Works on mssql

참고URL : https://stackoverflow.com/questions/1590799/delete-all-rows-in-a-table-based-on-another-table