Update a table by inserting a count of foreign key from another table

Question

I have two tables:

    ╔════════════════╗    ╔════════════════╗
    ║ ITEM           ║    ║ ITEM_TRACK     ║
    ╠════════════════╣    ╠════════════════╣
    ║ ID             ║    ║ ID             ║
    ║ GUID           ║    ║ ITEM_GUID      ║
    ║ COUNT1         ║    ║ CONTEXT        ║
    ║ ENDDATE        ║    ║                ║
    ╚════════════════╝    ╚════════════════╝

╔═════╦══════╦════════╗   ╔═════╦═══════════╦══════════╗
║ ID  ║ GUID ║ COUNT1 ║   ║ ID  ║ ITEM_GUID ║ CONTEXT  ║
╠═════╬══════╬════════╣   ╠═════╬═══════════╬══════════╣
║ 1   ║  aaa ║        ║   ║ 1   ║    abc    ║   ITEM   ║
║ 2   ║  bbb ║        ║   ║ 2   ║    aaa    ║   PAGE   ║
║ 3   ║  ccc ║        ║   ║ 3   ║    bbb    ║   ITEM   ║
║ 4   ║  abc ║        ║   ║ 4   ║    ccc    ║   ITEM   ║
╚═════╩══════╩════════╝   ║ 5   ║    abc    ║   ITEM   ║
                          ║ 6   ║    aaa    ║   ITEM   ║
                          ║ 7   ║    abc    ║   ITEM   ║
                          ║ 8   ║    ccc    ║   PAGE   ║
                          ╚═════╩═══════════╩══════════╝

What I'm trying to do is fill in the COUNT1 column in ITEM with the count of the number of times ITEM_GUID appears in ITEM_TRACK for all ITEM.GUIDs where ENDDATE is still in the future. I need to do this once an hour for all GUIDS in ITEM.

I can get the counts I need easily

SELECT ITEM_GUID, COUNT(*) from ITEM_TRACK GROUP BY ITEM_GUID;

What I don't know how to do is, how do I merge this with an INSERT INTO statement to automatically update all the items in the items table with the count based on their ENDDATE?

UPDATE: I have a working solution based on Aquillo's answer:

UPDATE ITEM a
SET COUNT1 = (SELECT COUNT(*) AS total FROM ITEM_TRACK b WHERE b.item_guid=a.guid);

Is there any other way to do this without a subquery?

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

You can insert from a select like this:

INSERT INTO myTable (foreignKey, countColumn) VALUES 
    SELECT ITEM_GUID, COUNT(*) from ITEM_TRACK GROUP BY ITEM_GUID;

In case you want to update, try something like this:

UPDATE from SELECT using SQL Server

edited May 23 '17 at 10:25

Community

1
1

answered Feb 10 '14 at 21:17

Menno

12,175
14
56
88

Ok. This isn't exactly what I want but it did help me get a working solution....Is there a way to do this without doing a subquery? – kasdega Feb 11 '14 at 17:45

score 0 · Answer 2 · answered Feb 10 '14 at 21:50

If you use INSERT INTO you'll put additional rows in your ITEM table, not update the existing ones. If this is what you meant then that's great, but if you want to update the existing ones, you'll need to use update. You do this by joining the table you want to update with the table you want to update from. However, in your case you want to update from an aggregation and so you need to create a table with the aggregated values. Try this:

UPDATE ITEM SET Count1 = temp.total
FROM Item
INNER JOIN (
    SELECT ITEM_GUID, COUNT(*) AS total
    FROM ITEM_TRACK
    GROUP BY ID) AS temp
ON Item.GUID = temp.ITEM_GUID
WHERE ENDDATE > NOW()

I've tried this on SQL Server (using GETDATE() instead of NOW()) to double check and it worked, I think it should work on MYSQL.

The syntax for this doesn't work with Mysql and I'm not entirely sure how to adjust it. This may work but someone smarter than me will have to evaluate and present a different syntax. — kasdega, Feb 11 '14 at 17:44

Update a table by inserting a count of foreign key from another table

2 Answers2