0

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 !

Robin
  • 15
  • 1
  • 6

1 Answers1

2

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

Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57
  • Hello, preceding SQL returns syntax error. `(` and `)` is missing. `table_1.column_3 = (SELECT COUNT .... )` would be worked. – Jason Heo Jan 03 '14 at 00:41
  • I am not sure to understand your reply Ilya Bursov : I use the Limit in order to not use directly count wich is slow. In your query, if i use directly count => slow request... But I don't understant the second request you print : `IF(count(distinct column_5)>300, 300, count(distinct column_5))` How can I using it ? – Robin Jan 03 '14 at 09:11
  • @Robin count in your query will be slow anyway, you can try to use indexes, but still distinct will be slow, `IF` construction can be used instead of `count(distinct...` to provide the same results – Iłya Bursov Jan 03 '14 at 15:28