Search code examples
sql-servernorthwind

Northwind query and modification

I would like to make such modification to Northwind database

Add Chocolade to all orders made by customer ALFKI, not including it yet

(I tried to used insert into, by unfortunately I failed.)

Solution

  • I've taken database schema from here (https://northwinddatabase.codeplex.com/). So, logic is simple: find all orders from customer 'ALFKI', find chocolate productID and add all this information to table Orders_Details.

    INSERT INTO Order_Details(orderID, productID, UniPrice, Quantity, Discount)
SELECT O.orderID,
       pr.productID,
       <your price>,
       <your quantity>,
       <your discount>
FROM Orders AS O, 
    (SELECT TOP 1 productID FROM Products WHERE productName like '%Chocolate%') AS pr
    JOIN Customers AS C ON O.customerID = C.customerID
WHERE C.companyName = 'ALFKI'