How do you update a sql table based on distinct matching counts in another table?

Question

I have 2 tables.

Contacts
+----+------+
| ID | Tier |
+----+------+
|  1 | Low  |
|  2 | High |
|  3 | Max  |
+----+------+

Events
+----+-----------+-----------+
| ID | EventType | GroupType |
+----+-----------+-----------+
|  1 | Open      | A         |
|  2 | Open      | A         |
|  3 | Open      | A         |
|  1 | Delete    | B         |
|  2 | Open      | B         |
|  3 | Open      | B         |
|  1 | Open      | A         |
|  3 | Open      | C         |
+----+-----------+-----------+

If Events contains 2 unique GroupTypes where EventType = 'Open' then the associated Contact record needs to be updated to a Tier of 'High', else if there are more than 2 I need to update to 'Max', else if there are fewer I need to update to 'Low'. (The above table shows correct tiers)

When attempting the below, I get "Error near Group". Can I group while updating? Is there a better way to get these results?

Update c
SET c.Tier = (CASE WHEN count(DISTINCT(e.GroupType)) > 2 THEN 'Max'
                   WHEN count(DISTINCT(e.GroupType)) = 2 THEN 'High'
                                                         ELSE 'Low' 
              END)
FROM Contacts c JOIN Events e on c.ID = e.ID
WHERE e.EventType = 'Open'
GROUP BY c.ID

Well, what's happening? Does the statement throw an error? Does it just not update the records? We are not parsers, so why didn't what you have work? — Clockwork-Muse, Apr 12 '16 at 22:08
Possible duplicate of [SQL Server Update Group by](http://stackoverflow.com/questions/2853403/sql-server-update-group-by) — paparazzo, Apr 12 '16 at 22:28
I get "Error near Group" when I try to run it. I see where I could use the answer in "SQL Server Update Group by", but it doesn't include a distinct within a group which is where I am having trouble... I think — Broom, Apr 13 '16 at 03:24

score 0 · Answer 1 · answered Apr 12 '16 at 22:05

0

This is exactly what VIEW is for in SQL. You can make it an indexed view, if necessary. As a concept, try to minimize data dependancy between tables; foreign keys are ok, but if you need data from this table to mtach data from a different table, use views (or calculayed columns) and not hard core UPDATED.

answered Apr 12 '16 at 22:05

Hila DG

688
4
12

While it's true that this is a derived value, and thus a view would be appropriate, that's not what the OP is asking for. There are a number of things that might prevent a view from being usable (such as source data size). – Clockwork-Muse Apr 12 '16 at 22:14
And perhaps the op didnt recognize that a view would be more appropriate than an update, so they asked for the wrong thing. The advice to use a view in preference seems quite reasonable. – Paul Maxwell Apr 12 '16 at 22:23
I'm looking to max out around 2 mil records in Contacts and 10 mil records in Events. I figured an update would be better since I only need to recalculate after loading new Event records from a flat file, but will need to retrieve Contact tiers thousands of times between updates. – Broom Apr 14 '16 at 20:54
Your other option (if you want to update directly a column in a table) would be to trigger an event every time you update the original table. Not only that it is more time-consuming, it is also a nightmare to write & maintain. View is sure to be updated automatically with the latest value, easier to write, read and maintain, and not triggering event. – Hila DG Apr 14 '16 at 22:50

score 0 · Accepted Answer · answered Apr 12 '16 at 23:20

You can't group by in your update statement. You just need to form the query separately that will give you the rows you need to update and join on that. You are updating based on ID, and the value you are setting depends on the number of open events for that ID, so form a query finding the number of open events by id:

-- query open event counts by contact id
SELECT ID, COUNT(*) AS OpenEventCount
FROM Events
WHERE EventType = 'Open'
GROUP BY ID

Now it's simple to link that to contacts and update:

UPDATE c
SET c.Tier = CASE
        WHEN COALESCE(ec.OpenEventCount, 0) > 2 then 'Max'
        WHEN COALESCE(ec.OpenEventCount, 0) = 2 then 'High'
        ELSE 'Low' 
    END
FROM Contacts c
    LEFT OUTER JOIN ( -- left join to update contacts with no open events
        SELECT ID, COUNT(*) AS OpenEventCount
        FROM Events
        WHERE EventType = 'Open'
        GROUP BY ID
    ) ec ON ec.ID = c.ID

Did exactly what I needed (minus the distinct, but that was easy to add) and was easy to understand. Thank you. — Broom, Apr 13 '16 at 03:33

How do you update a sql table based on distinct matching counts in another table?

2 Answers2