0

i need some help to get a fast update on my table in MySQL

Table 1     
id | value  
1    0  
2    0  
3    0 ...

Table 2  
t1_id | t2_id  
1       2  
1       3  
3       5 ...

Have about 150,000 rows in table 1, and about 1,3 million in table 2. I need set t1.value = 1 when t1.id exists in table 2.

update table1 t1, table2 t2
set value = 1
where t1.id = t2.id;

Without some distinct parameter, it will do many times for each id, making it slow to update all t1 rows.

Any help would be gladly accepted.

Herbert Habermann
  • 19
  • 2
  • 1
    See https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query - using a JOIN might perform faster – ADyson Jul 10 '17 at 14:15
  • thanks for you answer, but the join produces many entries for each value in table 1 too, so it will do that many update for each id, dont solve the problem. – Herbert Habermann Jul 10 '17 at 14:40

2 Answers2

1

what about:

UPDATE t1
   SET t1.value = 1
  FROM table_t1 t1
 WHERE EXISTS (SELECT 1 
                 FROM table_t2 t2 
                WHERE t2.id = t1.id
               )
Esteban P.
  • 2,789
  • 2
  • 27
  • 43
0

what about:

update table1 
set value=1
from table2
where table1.id=table2.t1_id
pisi1001
  • 89
  • 1
  • 5
  • thanks for your answer, @Esteban P. solution works great ! – Herbert Habermann Jul 10 '17 at 15:17
  • While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, as this reduces the readability of both the code and the explanations! – Blue Jul 10 '17 at 20:53