CODEWITHSUNDEEP
mysqlh2
Chan Chun Weng
Chan Chun Weng

Reputation: 896

h2 database 3 table delete in one query

I have 3 tables:-

customer(customer_id)
sales(sales_id, customer_id)
salesdetails(salesdetails_id, sales_id)

How can I delete the customer and also 2 other related table data in one query?

I stucked here:-

DELETE FROM customer, sales, salesdetails
USING customer, sales, salesdetails
WHERE customer.customer_id = sales.customer_id 
AND sales.sales_id = salesdetails.sales_id 
AND customer.customer_id = 'C0001';

Note:I am using and new to h2 database.

Upvotes: 1

Views: 1009

Answers (1)

Hedegare
Hedegare

Reputation: 2047

When you create your table add the delete method 'cascade'. Example:

Customer:

 CREATE TABLE customer(
  customer_id integer primary key auto_increment
);

Sales:

CREATE TABLE sales(
     sales_id integer primary key auto_increment,
     FOREIGN KEY (customer_id) REFERENCES customer(id)
       ON DELETE CASCADE
);

This way, when you delete a customer, all the related sales will be deleted too.

Upvotes: 2

Related Questions