SQLite: How to count references to each ROWID in a table, then insert the count

Question

I need some help writing a SQL statement. I have two tables in a SQLite database:

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  item_id INTEGER
);

CREATE TABLE items (
  item_id INTEGER PRIMARY KEY,
  ref_count INTEGER
);

I'm seeking a SQLite statement that will be like the following pseudocode:

for each row in items
    items[ row ].ref_count = SELECT COUNT(users.item_id) 
                               FROM users 
                              WHERE users.item_id=row;

Answer 1

It looks like you can use the REPLACE command as follows:

REPLACE INTO  items (item_id, ref_count)
SELECT        item_id, COUNT(*) AS ref_count
FROM          users
GROUP BY      item_id;

Test case:

INSERT INTO users VALUES (1, 1);
INSERT INTO users VALUES (2, 1);
INSERT INTO users VALUES (3, 1);
INSERT INTO users VALUES (4, 2);
INSERT INTO users VALUES (5, 2);
INSERT INTO users VALUES (6, 3);

Result after running the REPLACE query:

SELECT * FROM items;

item_id       ref_co
------------  ------
1             3     
2             2     
3             1      

Adding some further items:

INSERT INTO users VALUES (7, 1);
INSERT INTO users VALUES (8, 4);
INSERT INTO users VALUES (9, 4);

And after re-running the REPLACE query:

SELECT * FROM items;

item_id       ref_co
------------  ------
1             4     
2             2     
3             1     
4             2