Refresh table with counts from another

Question

I am trying to update a table with the counts of another table. I think I have the query structure correctly, but I keep getting SQL error:

UPDATE c SET c.sales = p.ProductCount FROM products c INNER JOIN (SELECT p_key, COUNT(*) AS ProductCount FROM sales GROUP BY p_key) p ON c.link = p.p_key 

The structure of two tables:

Products product_name (varchar), sales (int), link (char),

Selling email (char), p_key (char)

I just showed the key columns. Any help was appreciated.

Answer 1

You use the join syntax for T_SQL , in MySQL do this,

 UPDATE products c INNER JOIN ( SELECT p_key, COUNT(*) AS ProductCount FROM sales GROUP BY p_key ) p ON c.link = p.p_key SET c.sales = p.ProductCount 

Answer 2

Here is the correct syntax:

 UPDATE products c INNER JOIN ( SELECT p_key, COUNT(*) AS ProductCount FROM sales GROUP BY p_key ) p ON c.link = p.p_key SET c.sales = p.ProductCount