Updating column in sqlite table ... problems with groupby?

Question

-- Adding column Main Table
ALTER TABLE MainTable ADD COLUMN new_col REAL;


;WITH AddColTable AS (
       SELECT other_id, COUNT(main_id) as new_col
       FROM MainTable 
       GROUP BY other_id
)

UPDATE MainTable
SET new_col = (
    FROM MainTable
    INNER JOIN AddColTable
    ON MainTable.main_id == AddColTable.main_id

I'm trying to wrap my head around why this statement doesn't work. I have tried many different combinations of this using JOIN - in some cases even 3 nested levels unfortunately.

Is there an obvious way to fix this statement?

The goal is similar to others stated elsewhere, such as here: Updating column in table with sum of another table

However, the important difference seems to be operating on the table itself instead of adding a column based on another table.

I assumed this could be solved by using the with tbl as () statement, but it still doesn't work.

Example displaying the problem (in julia)

using SQLite, DBInterface, DataFrames
cxn = SQLite.DB(":memory:")

DBInterface.execute(cxn,"""
    CREATE TABLE
    MainTable(main_id INTEGER,
              other_id INTEGER)""")

DBInterface.execute(cxn,"""
    INSERT INTO MainTable
    VALUES (1,  13),
           (2, 213),
           (3,  13),
           (4, 342),
           (5, 213),
           (6, 213),
           (7,   4)""")

# Try to add new column which shows aggregate information about 'other_id'
DBInterface.execute(cxn, "
    -- Adding column Main Table
    ALTER TABLE MainTable ADD COLUMN new_col REAL;

    ;WITH AddColTable AS (
           SELECT other_id, COUNT(main_id) as new_col
           FROM MainTable 
           GROUP BY other_id
    )

    UPDATE MainTable
    SET new_col = (
        FROM MainTable
        INNER JOIN AddColTable
        ON MainTable.main_id == AddColTable.main_id)")


##### Show what happened #####

# Initial table
sql = "SELECT * FROM MainTable"
df = DataFrame(DBInterface.execute(cxn, sql))
println("Starting Table:\n $(df) \n\n")

# The aggregate information table, which 
#   should have a column added (joined) with the initial table
sql = "SELECT other_id, COUNT(main_id) as new_col
       FROM MainTable 
       GROUP BY other_id"
df = DataFrame(DBInterface.execute(cxn, sql))
println("Aggregate Information Table:\n $(df) \n\n")

# The actual table change using 'ALTER TABLE .. UPDATE .. SET'
sql = "SELECT * FROM MainTable"
df = DataFrame(DBInterface.execute(cxn, sql))
println("Main Table After Update:\n $(df) \n\n")

# The expected table
sql = "SELECT main_id, A.other_id, actual_new_col as new_col
       FROM MainTable
       INNER JOIN (
            SELECT other_id, COUNT(main_id) as actual_new_col
            FROM MainTable 
            GROUP BY other_id) AS A
       ON MainTable.other_id == A.other_id"
df = DataFrame(DBInterface.execute(cxn, sql))
println("*EXPECTED* Main Table After Update:\n $(df) \n\n")

DBInterface.close!(cxn)

This gives the following output:

Simple output vs expectation

Starting Table

 7×3 DataFrame
 Row │ main_id  other_id  new_col
     │ Int64    Int64     Missing
─────┼────────────────────────────
   1 │       1        13  missing
   2 │       2       213  missing
   3 │       3        13  missing
   4 │       4       342  missing
   5 │       5       213  missing
   6 │       6       213  missing
   7 │       7         4  missing 

Aggregate Information Table

 4×2 DataFrame
 Row │ other_id  new_col
     │ Int64     Int64
─────┼───────────────────
   1 │        4        1
   2 │       13        2
   3 │      213        3
   4 │      342        1 

Main Table After Update

 7×3 DataFrame
 Row │ main_id  other_id  new_col
     │ Int64    Int64     Missing
─────┼────────────────────────────
   1 │       1        13  missing
   2 │       2       213  missing
   3 │       3        13  missing
   4 │       4       342  missing
   5 │       5       213  missing
   6 │       6       213  missing
   7 │       7         4  missing 

EXPECTED Main Table After Update

 7×3 DataFrame
 Row │ main_id  other_id  new_col
     │ Int64    Int64     Int64
─────┼────────────────────────────
   1 │       1        13        2
   2 │       2       213        3
   3 │       3        13        2
   4 │       4       342        1
   5 │       5       213        3
   6 │       6       213        3
   7 │       7         4        1 

Answer 1

SQLite does not support an explicit join of the updated table in the UPDATE statement.

Since version 3.33.0+ SQLite supports the UPDATE...FROM... syntax:

WITH AddColTable AS (
  SELECT other_id, COUNT(main_id) as new_col
  FROM MainTable 
  GROUP BY other_id
)
UPDATE MainTable AS m
SET new_col = a.new_col
FROM AddColTable AS a
WHERE a.other_id = m.other_id;

See the demo.

For older versions the solution is a correlated subquery:

UPDATE MainTable AS m1
SET new_col = (
  SELECT COUNT(m2.main_id) 
  FROM MainTable AS m2 
  WHERE m2.other_id = m1.other_id
);

See the demo.