CODEWITHSUNDEEP
mysqlsql
Abs
Abs

Reputation: 57926

Update table with counts from another

I'm trying to update a table with the counts of another table. I think I've got the structure of the query right but I keep getting a 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 the two tables:

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

Sales email (char), p_key (char)

I've just shown the key columns.

Upvotes: 5

Views: 6196

Answers (2)

John Woo
John Woo

Reputation: 263733

You are using 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

Upvotes: 16

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

Here is the right 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

Upvotes: 6

Related Questions