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.
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).
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'
);
This question is related to
sql
oracle
sql-delete
Recently I learned of the following syntax:
DELETE (SELECT *
FROM productfilters pf
INNER JOIN product pr
ON pf.productid = pr.id
WHERE pf.id >= 200
AND pr.NAME = 'MARK')
I think it looks much cleaner then other proposed code.
Use a subquery in the where clause. For a delete query requirig a join, this example will delete rows that are unmatched in the joined table "docx_document" and that have a create date > 120 days in the "docs_documents" table.
delete from docs_documents d
where d.id in (
select a.id from docs_documents a
left join docx_document b on b.id = a.document_id
where b.id is null
and floor(sysdate - a.create_date) > 120
);
Source: Stackoverflow.com