Update, Delete and Insert Help

[cshannon]

I have an assignment in Access SQL on Updates, Deletes and Inserts. I need help on a few problems, I will write the problem below and explain how I need help.


Premiere has decided to no longer sell gas ranges, so they need to be removed from inventory. ALL orders for gas ranges must be removed also. However, if an order has other items on it, ONLY remove the order for the gas range. Modify the database.

So I have an Orders table, which lists OrderNumber, OrderDate, and CustomerNumber. I also have an OrderLine table, which lists OrderNumber, PartNumber, Number Ordered, and Total Price. I need to do this with one Delete statement, but not sure how to delete from two tables, especially when the query needs to delete gas ranges from orders that have other parts on them.



Another question:
All Season has just placed an order for 10 cordless drills, for a price of $115.00. Use 21624 for the order number and the current date for their order date. Modify the database.
So again, it seems like I need to insert different record types into different tables. I need to insert the order information into the Orders table (OrderNum, OrderDate) and also insert the OrderNum, PartNum, NumOrdered, and Price into the OrderLine table.

Thanks for any help, I'm really stuck here

[June7]

"Modify the database" to me means design changes. What you describe is just data management. Ideally, instead of running action SQLs, this data management would be done through forms.

A rather bizarre exercise. Deleting ALL orders for gas ranges would be impractical because that would distort historical data. What should really happen is to deactivate the product in the inventory table and cancel all UNFULFILLED orders for gas ranges. But teachers come up all kinds of weird stuff just to demonstrate something.

A single delete action will work only if tables have an established relationship and cascade delete is active. I don't see how one DELETE action is possible if you want to delete orders that are only for ranges as well as ranges from multi-item orders.

Deleting from Orders table is a bit complicated. If cascade delete is in effect, this should also delete the associated gas range records from OrderLine table
DELETE FROM Orders WHERE OrderNum IN (SELECT OrderNum FROM OrderLine WHERE PartNum=gas range part number AND Count(*)=1 GROUP BY OrderNum)

Now delete the gas ranges records from other orders.
DELETE FROM OrderLine WHERE PartNum = gas range part number

Now delete from inventory.
DELETE FROM Inventory WHERE somefield = 'gas range'

The order INSERT would be two actions.

INSERT INTO Orders(OrderNum, OrderDate, Customer) VALUES(21624, Date(), 'All Season')

INSERT INTO OrderLine(OrderNum, PartNum, NumOrdered, Price) VALUES(21624, some part number, 10, 115)