Reputation: 11
I have 3 tables, which are in a many-to-many relationship. I.e:
Create Table Product(ProductId number(18,0) NOT NULL);
Create Table Customer(CustomerId number(18,0) NOT NULL);
Create Table CustomerProduct(CustomerId number(18,0) NOT NULL,ProductId number(18,0) NOT NULL);
Since CustomerProduct table is referencing both Product and Customer table. I am trying to delete the data from CustomerProduct table.
I only can find something like:
DELETE FROM
(
SElECT CustomerProduct.* FROM CustomerProduct
INNER JOIN Product ON Product.ProductId = CustomerProduct.ProductId
INNER JOIN Customer ON Customer.CustomerId = CustomerProduct.CustomerId
WHERE Product.ProductId = 1 AND Customer.CustomerId = 7
);
NOTE: there is no CASCADE delete defined on foreign keys... Oracle doesn't allow me to do something I can do in SQL SERVER
DELETE A
FROM A
INNER JOIN B on a.Id = b.id
WHERE b.Id = 2.....
Upvotes: 1
Views: 1590
Reputation: 1190
I came across the same problem. I resolved very nice with a trigger.
create or replace TRIGGER "AUTO_DELETE_PRODUCTS"
BEFORE DELETE ON Product
FOR EACH ROW
BEGIN
DELETE FROM CustomerProduct WHERE CustomerProduct.ProductId = :old.ProductId;
END;
You can can do this for the rest of the tables. And this way when you delete a record in one of them the info will be deleted all over the tables.
Upvotes: 1
Reputation: 103
I just spent a couple hours trying to figure this out and you have the correct answer in your question. Though, if you change your query to where the relevant conditions do not exist in the relationship table then it makes a bit more sense why the join would be needed.
Something like:
CREATE TABLE Product(ProductId number(18,0) NOT NULL, Name VARCHAR2(30));
CREATE TABLE Customer(CustomerId number(18,0) NOT NULL, Name VARCHAR2(30));
DELETE FROM
(SELECT cp.*
FROM CustomerProduct cp
INNER JOIN Product p ON p.ProductId = cp.ProductId
INNER JOIN Customer c ON c.CustomerId = cp.CustomerId
WHERE c.Name LIKE 'TEST%' AND p.Name IS NULL);
Upvotes: 0
Reputation: 11
I don't understand what you want to do. Deleting from table CustomerProduct can be done with a simple delete statement as follows:
delete CustomerProduct
where ProductId = 1 and CustomerId = 7
Upvotes: 1