使用“加入"删除在Oracle sql查询中 [英] Delete with "Join" in Oracle sql Query

问题描述

我对Oracle SQL查询并不十分了解，因此在删除表中的某些行时遇到一个问题，该行必须满足一个约束，该约束包括另一个(联接)表的字段.换句话说，我想编写一个查询来删除包括JOIN的行.

I am not deeply acquainted with Oracle Sql Queries, therefore I face a problem on deleting some rows from a table which must fulfill a constraint which includes fields of another (joining) table. In other words I want to write a query to delete rows including JOIN.

在我的情况下，我有一个表ProductFilters和另一个表Products联接在字段ProductFilters.productID = Products.ID上.我想从ProductFilters中删除具有大于或等于200的ID的行，并且它们引用的产品的名称为'Mark'(名称是Product中的字段).

In my case I have a table ProductFilters and another table Products joined on fields ProductFilters.productID = Products.ID. I want to delete the rows from ProductFilters having an ID higher or equal to 200 and the product they refer has the name 'Mark' (name is a field in Product).

我想首先被告知Oracle中的删除查询中JOIN是否可以接受.如果不是这样，我应该如何修改此查询以使其起作用，因为在该表格上我会收到错误消息:

I would like to be informed initially if JOIN is acceptable in a Delete Query in Oracle. If not how should I modify this Query in order to make it work, since on that form I receive an error:

DELETE From PRODUCTFILTERS pf 
where pf.id>=200 
And pf.rowid in 
(
     Select rowid from PRODUCTFILTERS 
     inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID 
     And PRODUCTS.NAME= 'Mark'
);       

推荐答案

基于我在上面的评论中链接的答案，这应该可行:

Based on the answer I linked to in my comment above, this should work:

delete from
(
select pf.* From PRODUCTFILTERS pf 
where pf.id>=200 
And pf.rowid in 
  (
     Select rowid from PRODUCTFILTERS 
     inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID 
     And PRODUCTS.NAME= 'Mark'
  )
); 

或

delete from PRODUCTFILTERS where rowid in
(
select pf.rowid From PRODUCTFILTERS pf 
where pf.id>=200 
And pf.rowid in 
  (
     Select PRODUCTFILTERS.rowid from PRODUCTFILTERS 
     inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID 
     And PRODUCTS.NAME= 'Mark'
  )
); 

这篇关于使用“加入"删除在Oracle sql查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！