Update table with an alias on where clause

Question

I need a bit of assistance on an update statement. I have the following query

  update totals
  set contacts = (SELECT 
      COUNT(*)
        FROM contact_events c
        JOIN users u
        ON c.user = u.id 
        GROUP BY c.user)
  where c.user = ext_id;

But when I run it I get 'unknown column c.user in where clause'.

However, I can't seem to apply an alias on that join so I must be doing something wrong here with syntax.

The alias `c` is in your subquery, therefore it's out of context in your main query. That is always the case with subqueries where the aliases used inside the parentheses are only used within the parentheses. Your query will need to be rewritten to use proper syntax for an UPDATE statement that references another table. — JNevill, Jan 12 '18 at 16:37
Thank you, I was having trouble finding another question for that but that looks perfect — Geoff_S, Jan 12 '18 at 16:39

score 1 · Accepted Answer · answered Jan 12 '18 at 16:43

You have to use JOIN to perform such an operation

UPDATE totals t
INNER JOIN (SELECT c.user, 
                COUNT(*) tot
            FROM contact_events c
        JOIN users u ON c.user = u.id GROUP BY c.user) u ON u.user = t.ext_id
SET t.contacts = u.tot

Update table with an alias on where clause

1 Answers1