Updating more than one table with an inner-join?

[Wallbanged]

Ok, I'm making an fairly simple ecommerce site using asp.net and access with 4 database tables. Pic below.

I'm required to produce a page which displays all the orders processed. To do this I made a simple select inner join statement

SELECT Orders.*, ProductsOrders.ProductID, ProductsOrders.Quantity, ProductsOrders.TotalPrice, Products.Name, Products.Manufacturer, Customers.First_Name, Customers.Second_Name, Customers.Address_Line1, Customers.Address_Line2, Customers.[Town/City], Customers.Post_code, Customers.Card_Type, Customers.Cardholders_Name, Customers.Card_Number, Customers.Start_Date, Customers.Expiry_Date, Customers.Security_Pin
FROM Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN ProductsOrders ON Orders.OrderID = ProductsOrders.OrderID) ON Products.ProductID = ProductsOrders.ProductID

Doing this produces a table with all the inner joined fields showing in a single table. Now i was wondering if it's possible to update the inner joined fields with an update statement. I'll also be using asp.nets listview feature to do the updating inserting.

Also wondering if it's possible to insert data into those inner joined fields also.

If you need any more information then let me know.

 

[SOS]

No, you can't update like that. You have multiple one-to-many situations. You need to handle them in chunks.

 

[Wallbanged]

No, you can't update like that. You have multiple one-to-many situations. You need to handle them in chunks.

My database structure is probably wrong but it was the only way i could think of to get a many to many between orders and products.

So at checkout I'm looking at an insert for the customers details(Customer table), an insert for the price and quantity(ProductsOrders table)? So then how does the Orders table get filled with the correct data from the foreign keys?

Thanks.