SQLite中基于Transaction表生成Person表计算列的语法错误及批量更新TotalPrice/TotalPaid的方法咨询

Alright, let's break this down for you. The core issue you're facing is that SQLite's generated columns (the AS (...) syntax) can't directly reference other tables or run cross-row aggregate queries like SUM() — generated columns only operate on values from the same row in the current table. Let's fix this with three practical approaches tailored to your needs:

1. One-Time Batch Update (For Existing Data) #

If you just need to populate the TotalPrice and TotalPaid columns in your existing Person table once, use this UPDATE statement with correlated subqueries:

UPDATE Person
SET 
    TotalPrice = (SELECT SUM(Price) FROM Transaction WHERE Transaction.Name = Person.Name),
    TotalPaid = (SELECT SUM(AmountPaid) FROM Transaction WHERE Transaction.Name = Person.Name);

How it works: #

For every row in the Person table, the subqueries find all matching records in the Transaction table (by Name), calculate the sum of Price and AmountPaid, then assign those totals to the corresponding columns in Person.

2. Triggers for Automatic Real-Time Updates #

If you want Person to stay in sync with future changes to the Transaction table (new entries, edits, or deletions), use triggers to automate the updates. Create these three triggers:

Trigger for New Transactions #

CREATE TRIGGER update_person_on_transaction_insert
AFTER INSERT ON Transaction
BEGIN
    UPDATE Person
    SET 
        TotalPrice = TotalPrice + NEW.Price,
        TotalPaid = TotalPaid + NEW.AmountPaid
    WHERE Name = NEW.Name;
END;

Trigger for Updated Transactions #

CREATE TRIGGER update_person_on_transaction_update
AFTER UPDATE ON Transaction
BEGIN
    UPDATE Person
    SET 
        TotalPrice = TotalPrice - OLD.Price + NEW.Price,
        TotalPaid = TotalPaid - OLD.AmountPaid + NEW.AmountPaid
    WHERE Name = OLD.Name;
END;

Trigger for Deleted Transactions #

CREATE TRIGGER update_person_on_transaction_delete
AFTER DELETE ON Transaction
BEGIN
    UPDATE Person
    SET 
        TotalPrice = TotalPrice - OLD.Price,
        TotalPaid = TotalPaid - OLD.AmountPaid
    WHERE Name = OLD.Name;
END;

How it works: #

These triggers fire automatically whenever a change is made to the Transaction table, adjusting the corresponding TotalPrice and TotalPaid values in Person to reflect the new state.

3. Use a View Instead of Stored Columns #

If you don't need to store TotalPrice and TotalPaid permanently (and prefer real-time calculated values), create a view that combines data from both tables:

CREATE VIEW PersonWithTotals AS
SELECT 
    p.ID,
    p.Name,
    COALESCE(SUM(t.Price), 0.00) AS TotalPrice,
    COALESCE(SUM(t.AmountPaid), 0.00) AS TotalPaid,
    COALESCE(SUM(t.Price), 0.00) - COALESCE(SUM(t.AmountPaid), 0.00) AS PaymentDue
FROM Person p
LEFT JOIN Transaction t ON p.Name = t.Name
GROUP BY p.ID, p.Name;

How it works: #

The view dynamically calculates the totals every time you query it, so it always shows the latest data without needing to maintain stored values. Use it just like a table:

SELECT * FROM PersonWithTotals;

Why Your Original Generated Column Attempts Failed #

SQLite's generated columns are strictly row-level — they can only reference other columns in the same row of the table, not data from other tables or aggregate results across multiple rows. Your attempts to use SUM() with a cross-table WHERE clause violate this restriction, hence the syntax errors.

内容的提问来源于stack exchange，提问作者The.N00B.One