Mysql -- UPDATE table SET column = SELECT COUNT(*) FROM (SELECT * FROM table2 WHERE table2.id = table.id)) 不可能

我有两个包含很多行的表，我需要为第一个 (table_1) 中的每一行维护索引"信息.所以我写了一个查询不直接使用 COUNT() [这是慢，慢，慢].所以我尝试:

<上一页>更新 table_1 SET table_1.column_3 = (选择计数(*)从(SELECT DISTINCT column_5 FROM table_2 WHERE table_2.id_t1 = table_1.id LIMIT 300) 吨)

但是 MySQL 回答我 table_1.id 在 where 子句中是未知的 (#1054)

你知道如何在 where 子句中传递 table_1.id 吗?或者其他方式来实现我的目标?

谢谢你帮助我！

解决方案

问题是table_1离内部查询太远了，使用:

UPDATE table_1 SET table_1.column_3 =(SELECT count(DISTINCT column_5) FROM table_2 WHERE table_2.id_t1 = table_1.id);

我看到你正在使用 LIMIT，不确定你是否需要它，无论如何你可以模仿它:

IF(count(distinct column_5)>300, 300, count(distinct column_5))

I have two tables with a lot of rows and I need to maintain "index" informations for each row in the first (table_1). So I wrote a query to not use directly COUNT() [wich is slow, slow, slow]. So I try :

UPDATE table_1 SET table_1.column_3 = (  
    SELECT COUNT(*) FROM (
        SELECT DISTINCT column_5 FROM table_2 WHERE table_2.id_t1 = table_1.id LIMIT 300
    ) t
)

But MySQL answering me that table_1.id is unknow in where clause (#1054)

Did you know how to passs table_1.id in the where clause ? Or other way to get my goal ?

Thank you for helping me !

解决方案

the problem is because table_1 is too far from inner query, use:

UPDATE table_1 SET table_1.column_3 = 
(SELECT count(DISTINCT column_5) FROM table_2 WHERE table_2.id_t1 = table_1.id);

as I see you're using LIMIT, not really sure that you need it, anyway you can emulate it:

IF(count(distinct column_5)>300, 300, count(distinct column_5))