MySQL-更新表SET列= SELECT COUNT(*)FROM(SELECT * FROM table2 WHERE table2.id = table.id))不可能 [英] Mysql -- UPDATE table SET column = SELECT COUNT(*) FROM ( SELECT * FROM table2 WHERE table2.id = table.id ) ) Impossible

问题描述

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

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
)

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

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

您知道如何在where子句中传递table_1.id吗?还是其他实现我目标的方法?

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

感谢您对我的帮助！

推荐答案

问题是因为table_1离内部查询太远，请使用:

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);

正如我所看到的，您正在使用LIMIT，但无论如何都可以模拟它，所以不确定自己是否需要它:

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))

这篇关于MySQL-更新表SET列= SELECT COUNT(*)FROM(SELECT * FROM table2 WHERE table2.id = table.id))不可能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！