SQLite:根据另一个表中设置的条件更新一列

Question

我是 SQL 的菜鸟，我才刚刚开始使用 SQLite，这是我在这里的第一篇文章，所以请原谅我固有的缺乏理解。本科毕业后我一直在自学，所以请多多包涵，哈哈。

我已经设法使以下查询工作，但前提是所有数据都是同一个表的一部分。我已经将我的两个表分成三个单独的表:widgetCustomer、widgetSale 和widgetOrderInfo。

    UPDATE widgetOrderInfo
    SET extendedcost = (cost * qty) * (0.80) 
    WHERE widgetCustomer.IsASenior = 'Y';
            -- Seniors get a 20 percent discount;

^^ 这是我遇到麻烦的部分。每当我使用此查询时，SQLite 都会返回“没有这样的列:IsASenior”。

--注意:IsASenior 是 widgetCustomer 中的一列，根据代码中前面的触发器设置为“Y”或“N”。使用以下查询，仅使用 widgetOrderInfo 表中的数据，我就可以非常轻松地进行基础乘法工作:

    UPDATE widgetOrderInfo 
    SET extendedcost = (cost * qty);

非常基本，我知道，但我不确定我必须做什么才能让我的 widgetOrderInfo 表确认 widgetCustomer 的“IsASenior”列中的数据。

我是否必须使用 JOIN 才能在 UPDATE 语句中访问两个表之间的数据？

任何帮助都会很棒!谢谢!

我目前正在执行的查询的完整列表和顺序(很多占位符；WIP)

CREATE TABLE widgetCustomer 
    ( id INTEGER PRIMARY KEY, name TEXT, age INT, IsASenior TEXT, last_order_id INT, soItemID INT );
CREATE TABLE widgetSale 
    ( id INTEGER PRIMARY KEY, item_id INT, item_name TEXT, customer_id INT, quan INT, price MONEY(8,2) );
CREATE TABLE widgetOrderInfo
    ( id INTEGER PRIMARY KEY, salesOrderID INT, cost MONEY(8,2), qty INT, extendedcost MONEY(8,2) )

;

CREATE TRIGGER SeniorCheck AFTER INSERT ON widgetCustomer
    BEGIN
        UPDATE widgetCustomer 
        SET IsASenior = 'Y' WHERE age >= 65;
        UPDATE widgetCustomer 
        SET IsASenior = 'N' WHERE age < 65;
    END

;

INSERT INTO widgetCustomer (name, age, soItemID) 
    VALUES ('Ian', 24, 3);
INSERT INTO widgetCustomer (name, age, soItemID) 
    VALUES ('Andrew', 29, 2);
INSERT INTO widgetCustomer (name, age, soItemID) 
    VALUES ('John', 65, 1);
INSERT INTO widgetCustomer (name, age, soItemID) 
    VALUES ('Kathy', 60, 4)

;

CREATE TRIGGER newWidgetSale AFTER INSERT ON widgetSale
    BEGIN
        UPDATE widgetCustomer 
        SET last_order_id = NEW.id 
        WHERE widgetCustomer.id = NEW.customer_id;
    END

;

INSERT INTO widgetSale (item_id, item_name, customer_id, quan, price) 
    VALUES (1, 'Blue Brick', 3, 50, 9.95);
INSERT INTO widgetSale (item_id, item_name, customer_id, quan, price) 
    VALUES (2, 'Red Brick', 2, 30, 4.95);
INSERT INTO widgetSale (item_id, item_name, customer_id, quan, price) 
    VALUES (3, 'Black Brick', 1, 24, 9.95);
INSERT INTO widgetSale (item_id, item_name, customer_id, quan, price) 
    VALUES (4, 'Yellow Brick', 4, 30, 9.95)

;

CREATE TRIGGER SubtractQuan AFTER INSERT ON widgetOrderInfo FOR EACH ROW
    BEGIN
        UPDATE widgetSale
        SET quan = (quan - New.qty)
        WHERE customer_id = New.id;
    END

;

INSERT INTO widgetOrderInfo (salesOrderID, cost, qty) 
        VALUES (283001, 9.95, 4);
INSERT INTO widgetOrderInfo (salesOrderID, cost, qty) 
        VALUES (283002, 4.95, 8);
INSERT INTO widgetOrderInfo (salesOrderID, cost, qty) 
        VALUES (283003, 9.95, 5);
INSERT INTO widgetOrderInfo (salesOrderID, cost, qty) 
        VALUES (283004, 9.95, 15)

;

    UPDATE widgetOrderInfo 
    SET extendedcost = (cost * qty)

;

SELECT 
    CASE
        WHEN qty BETWEEN 5 and 7 THEN 'parcel'
        WHEN qty BETWEEN 8 and 14 THEN 'package'
        WHEN qty BETWEEN 15  and 30 THEN 'box'
        WHEN qty BETWEEN 31 and 99 THEN 'crate'
    ELSE 'individually wrapped'
END
    AS PackagingBasedOnPurchaseSize,
    COUNT(*) qty FROM widgetOrderInfo
    GROUP BY 
    CASE
        WHEN qty BETWEEN 5 and 7 THEN 'parcel'
        WHEN qty BETWEEN 8 and 14 THEN 'package'
        WHEN qty BETWEEN 15  and 30 THEN 'box'
        WHEN qty BETWEEN 31 and 99 THEN 'crate'
    ELSE 'individually wrapped'
END

;

    SELECT * FROM widgetSale;
    SELECT * FROM widgetCustomer;
    SELECT * FROM widgetOrderInfo;

如果需要，我可以提供指向我的整个数据库的链接。

Answer 1

在 UPDATE 语句中，您只能直接访问更新后的表本身。
要访问其他表，您需要一个子查询。

要查找与高级客户关联的订单信息行，您可以使用相关子查询为每个订单信息行查找相应的客户行:

UPDATE widgetOrderInfo
SET extendedcost = (cost * qty) * (0.80)
WHERE (SELECT IsASenior
       FROM widgetCustomer
       JOIN widgetSale ON widgetCustomer.id = widgetSale.customer_id
       WHERE widgetSale.id = widgetOrderInfo.salesOrderID
      ) = 'Y';

或者先获取所有高级客户，然后检查该集合中的订单信息:

UPDATE widgetOrderInfo
SET extendedcost = (cost * qty) * (0.80)
WHERE salesOrderID IN (SELECT widgetSale.id
                       FROM widgetCustomer
                       JOIN widgetSale ON widgetCustomer.id = widgetSale.customer_id
                       WHERE IsASenior = 'Y');