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

Question

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 ='2ManySnakes@gmail.com' -- 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.

I hope it isn't real email. Posting real data (even email) is unprofessional. — Lukasz Szozda, Mar 13 '16 at 14:25
Won't work. Do it in two steps, i.e. `delete from customer where...;` followed by a separate `delete from orders where...;`. Put it in a transaction if you like. — PerlDuck, Mar 13 '16 at 14:26
DELETE FROM Customer WHERE Email ='2ManySnakes@gmail.com'; DELETE FROM `Order` WHERE O_CustomerID = 2; — Deval Khandelwal, Mar 13 '16 at 14:27
If there happens to be a FK relationship between customer and order, you'll likely need to execute the two deletes first from order then customer'; if cascade delete is not enabled. — xQbert, Mar 13 '16 at 14:38

score 2 · Accepted Answer · answered Mar 13 '16 at 14:27

2

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 ='2ManySnakes@gmail.com';
DELETE FROM `Order` WHERE O_CustomerID = 2;

answered Mar 13 '16 at 14:27

Gouda Elalfy

6,888
1
26
38

score 1 · Answer 2 · answered Mar 13 '16 at 14:29

1

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.

answered Mar 13 '16 at 14:29

Gordon Linoff

1,242,037
58
646
786

As always, very good answer of yours! I always feel confident when I see your name :-) Given the structure of the OP's tables shown [here](http://stackoverflow.com/q/35970170/5830574), the constraints are `on delete restrict`. – PerlDuck Mar 13 '16 at 14:34
It's a school assignment I was told to use the email as an identifier to delete the row. – LovesPie Mar 13 '16 at 14:34
@LovesPie . . . You can use the email, it just looks really awkward. – Gordon Linoff Mar 14 '16 at 01:45

score 0 · Answer 3 · answered Mar 13 '16 at 14:47

0

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;

answered Mar 13 '16 at 14:47

ZakiaSalod

1
1

Please re-think your first statement. It has 2 syntax errors. – PerlDuck Mar 13 '16 at 14:50

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

3 Answers3