Update Faster

[Treterpeter]

Hi guys,

 

Say I got two tables,

• Table1 - columns A, B, C, flag - approx 200 000 rows
  
• Table2 - columns A, B, C - approx 20 000 rows
  

What I want to do is:

• Update Table1
  Set flag = 1
  Where "at least one row from Table2 has the same entries in columns
  A, B and C"
  

Right now I am solving this with a "select distinct A, B, C from Table2" and transform this into a long where condition (with php). afterwards I apply the above update with this where condition. However this aproche needs about 5 minutes to run and I believe this could be much faster when I just know the right MySql command for this.

 

Can anyone help me?

 

cheers,

Peter

[PFMaBiSmAd]

The quickest way of doing something like inserting new rows/updating (replacing) existing rows for something like an inventory, is to use a multi-value REPLACE query -

REPLACE tbl_name (col_name,...) VALUES (value,...),(...),...

Edited January 30, 2013 by PFMaBiSmAd

[Barand]

or use a join

 

UPDATE table1
INNER JOIN table2
   ON table1.A = table2.A
   AND table1.B = table2.B
   AND table1.C = table2.C
SET table1.flag = 1

[Treterpeter]

Thanks, will try that.