Northwind查询和修改

Question

我想对Northwind数据库做这样的修改

将Chocolade添加到客户ALFKI的所有订单中，但不包括在内

(我尝试使用insert into，不幸的是我失败了。)

Answer 1

我从这里获取了数据库模式(https://northwinddatabase.codeplex.com/)。因此，逻辑很简单:找到来自客户'ALFKI‘的所有订单，找到巧克力productID，并将所有这些信息添加到表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'