Android中SQLite从表A分组更新表B的实现及语法错误解决

Hey there, let's sort out this SQLite issue you're facing. Your goal is to group Table A by BOM and Date, sum the Consumption values, and sync those results to Table B. The code you wrote has some syntax and logical issues, so here's how to fix it properly:

Scenario 1: Replace/Insert All Aggregated Data into Table B #

If Table B should hold the full set of aggregated results (and you don't need to keep existing non-matching records in Table B), you can either clear the table first then insert, or use REPLACE INTO if you have a unique constraint on BOM + Date:

Option 1: Truncate and Insert #

-- Clear existing data in Table B (skip this if you need to retain unrelated records)
DELETE FROM Table_B;

-- Insert grouped sums from Table A into Table B
INSERT INTO Table_B (BOM, Date, Consumption)
SELECT BOM, Date, SUM(Consumption)
FROM Table_A
GROUP BY BOM, Date;

Option 2: Use REPLACE INTO (For Unique Key Cases) #

If you've set a composite unique constraint on (BOM, Date) in Table B, REPLACE INTO will automatically overwrite existing matching records and insert new ones:

REPLACE INTO Table_B (BOM, Date, Consumption)
SELECT BOM, Date, SUM(Consumption)
FROM Table_A
GROUP BY BOM, Date;

Scenario 2: Only Update Existing Records in Table B #

If Table B already has some BOM + Date entries and you just want to update their Consumption values to the summed totals from Table A, use an UPDATE with a subquery and EXISTS check:

UPDATE Table_B
SET Consumption = (
    SELECT SUM(Consumption)
    FROM Table_A
    WHERE Table_A.BOM = Table_B.BOM 
      AND Table_A.Date = Table_B.Date
)
WHERE EXISTS (
    SELECT 1
    FROM Table_A
    WHERE Table_A.BOM = Table_B.BOM 
      AND Table_A.Date = Table_B.Date
);

What Was Wrong with Your Original Code? #

Your initial UPDATE statement had a few critical issues:

• Incorrect UPDATE syntax: SQLite's UPDATE requires the format UPDATE table SET column = value ... — your code tried to set multiple columns in an invalid way and included a subquery incorrectly.
• Missing grouping: Using sum(*) would sum all rows in Table A instead of grouping by each BOM + Date pair.
• No table association: You didn't link Table A and Table B to ensure the correct rows are updated.

With the above fixes, Table B will end up with your expected results:

BOM | Date | Consumption
salt | Mar 8, 2019 | 1.2
pepper | Mar 8, 2019 | 0.3
Rice | Mar 8, 2019 | 0.8

内容的提问来源于stack exchange，提问作者Noruwa