Matt Johnson Matt Johnson - 3 years ago 78
SQL Question

list of states with the total number of units that have been sold to that state

I am still new to SQL, and have to complete the tasks at hand. The task is:
A "fake" user has made a few orders. DELETE the user account along with all of their orders.
This means delete from 3 tables:
ORDERDETAIL
ORDERS
CUSTOMERS

ORDERDETAIL and ORDERS have a FK of ORDERID, and ORDERS and CUSTOMERS share a FK of CUSTOMERID. I started to use this code:

DELETE ORDERDETAIL.ORDERID
FROM ORDERDETAIL
INNER JOIN ORDERS
ON ORDERDETAIL.ORDERID = ORDERS.ORDERID
WHERE ORDERS.CUSTOMERID = '12341';


Just to start, but even this fails.

What code can I use to delete all rows that share the same ORDERID in ORDERDETAIL and ORDERS as well as CUSTOMERID from CUSTOMER and ORDERS?

Thanks for any assistance!

Answer Source

You want to use a subquery to find the orderids that you want to delete in a child table:

delete from orderdetail where orderid in (
     select orderid from orders
      where customerid = '12341'
);

Then you're able to delete the corresponding orders:

delete from orders
 where customerid = '12341';

If your tables are set up with a cascading delete, you can just execute the 2nd delete statement (without first going to execute the first statement).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download