CODEWITHSUNDEEP
mysql
LovesPie
LovesPie

Reputation: 117

How would I delete all traces of a specific "Customer" from 2 tables? MySQL

I have 2 tables, Customer and Order.

I wish to delete the rows in which that particular Customer appears in. I have tried this,

DELETE FROM Customer
WHERE Email ='[email protected]' -- I was asked to associate the customer with an email. 
UNION
DELETE FROM `Order`
WHERE O_CustomerID = 2;

Not sure if this will work. But I just want to know if the syntax is correct.

Upvotes: 1

Views: 228

Answers (3)

ZakiaSalod
ZakiaSalod

Reputation: 1

Is there a foreign key between the Customer and Order table?

If yes, you can delete from Order where (select from customer where customer_id = 2); Commit;

Then delete from customer where customer_id = 2; Commit;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270593

You just use two SQL statements. I presume these would be:

DELETE c FROM Customer c
WHERE CustomerId = 2;

DELETE o FROM `Order` o
WHERE O_CustomerID = 2;

There is no need to use the email for one table and the customer id for the other -- use the same identifier for both.

Note that you can also use cascading foreign key constraints. That would make it possible to delete the customer record from the customer table, and all related tables for that customer would also be deleted.

Upvotes: 1

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

union used for joining 2 queries vertically, so it is no sense here in your query, you run 2 queries like this:

DELETE FROM Customer WHERE Email ='[email protected]';
DELETE FROM `Order` WHERE O_CustomerID = 2;

Upvotes: 2

Related Questions