SQL query to insert aggregated data into existing column using columns from same table

Question

I have a table in a sqlite db containing an individual's income, that individual's household identifier, and an empty column for household income. I'm trying to run a query that will fill in household income based on a subquery which returns sum of income grouped by household identifier. My query:

INSERT INTO customer_data (household_income)
    SELECT t.total
    FROM (SELECT household_id, sum(individual_income)
          FROM customer_data
          GROUP BY household_id
         ) t
    WHERE customer_data.household_id = t.household_id;

but my query is failing saying it 'cannot find customer_data.household_id.' I'm having trouble finding examples where the data inserted is coming from the same table and requires a subquery to get aggregates based on grouping. Any ideas on my approach?

Answer 1

You need an update. It looks like a correlated subquery will do what you want:

UPDATE customer_data 
    SET household_income = (SELECT sum(cd2.individual_income)
                            FROM customer_data cd2
                            WHERE customer_data.household_id = cd2.household_id
                           );