user2667066 user2667066 - 4 months ago 60
MySQL Question

mysql update with select count() from inner join

I have the following select statement that counts the number of times a value appears in a joined table:

SELECT table1.idx, count(table1.idx) FROM
(table2 INNER JOIN table1 ON table1.id=table2.id AND table2.value = 'target')
GROUP BY table1.idx;


Now I want to update a third table with these results, setting the counts where table3.idx = table1.idx. I've tried

UPDATE table3 SET count_col=joined_table.c FROM
(SELECT table1.idx, count(table1.idx) FROM
(table2 INNER JOIN table1 ON table1.id=table2.id AND table2.value = 'target')
GROUP BY table1.idx
) joined_table WHERE table3.idx = table1.idx;


as recommended at UPDATE from SELECT using SQL Server, and also tried

UPDATE table3 SET count_col=(
SELECT COUNT(table1.idx)
FROM (table1 INNER JOIN table2 ON table1.id=table2.id AND table2.value = 'target')
GROUP BY table1.idx
WHERE table3.idx = table1.idx);


as recommended in MySQL - UPDATE query based on SELECT Query, but neither of them work. What's the correct syntax?

Answer
UPDATE table3 
JOIN 
(
  SELECT table1.idx, count(table1.idx) as cnt
  FROM table2 
  INNER JOIN table1 ON table1.id = table2.id 
                   AND table2.value = 'target'
  GROUP BY table1.idx
) joined_table ON table3.idx = joined_table.idx
SET count_col = joined_table.cnt
Comments