Search code examples
sqloraclesql-deletedelete-row

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!

Solution

  • 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).