Conditionally UPDATE a column in one big table from a column in another big table

[bubulle]

Hi, all.
Here is my problem:

Let's say i have table1 with columns a,b,c and table2 with cols x,y,z. Some of columns contain the same type of data from one table to the other, but others are totally different. Both tables have a very large number of rows (~70k).

Now, i need to replicate in table1 a column that exists only in table2, so i create a new column in table1, let's name it d, and i want to conditionally import column z from table2 in column d from table1. Conditionally means where a = x and b=y, for example.

i created this query:

UPDATE table1
SET d = (SELECT z FROM table2 WHERE table1.a=table2.x AND table1.b=table2.y)

i tested this query on a similar set of tables with a few rows and it does the job, but really slow (took ~1 minute to process 300 rows). I'm sure that if i run it on the 2 big tables it will take forever to complete or may not even finish at all.

any better ideas, faster alternatives ? (deleting and re-creating tables in not an option)
thanks

PS: i use mysql 5.0.26 on Win XP SP2 and MySQL Query Browser. I also tried to query from a PHP script, takes just as much.

[pradeep kaltari]

Hi, all.
Here is my problem:

Let's say i have table1 with columns a,b,c and table2 with cols x,y,z. Some of columns contain the same type of data from one table to the other, but others are totally different. Both tables have a very large number of rows (~70k).

Now, i need to replicate in table1 a column that exists only in table2, so i create a new column in table1, let's name it d, and i want to conditionally import column z from table2 in column d from table1. Conditionally means where a = x and b=y, for example.

i created this query:

UPDATE table1
SET d = (SELECT z FROM table2 WHERE table1.a=table2.x AND table1.b=table2.y)

i tested this query on a similar set of tables with a few rows and it does the job, but really slow (took ~1 minute to process 300 rows). I'm sure that if i run it on the 2 big tables it will take forever to complete or may not even finish at all.

any better ideas, faster alternatives ? (deleting and re-creating tables in not an option)
thanks

PS: i use mysql 5.0.26 on Win XP SP2 and MySQL Query Browser. I also tried to query from a PHP script, takes just as much.

Hi bubulle,
Welcome to TSDN.
I can suggest an alternative.

UPDATE table1 LEFT OUTER JOIN table2 ON table1.a=table2.x AND table1.b=table2.y
SET table1.d=table2.z
 

You can also add WHERE table2.z IS NOT NULL. Please get back with performance details. I hope this helps.

Regards,
Pradeep

[bubulle]

Hi bubulle,
Welcome to TSDN.
I can suggest an alternative.

UPDATE table1 LEFT OUTER JOIN table2 ON table1.a=table2.x AND table1.b=table2.y
SET table1.d=table2.z
 

You can also add WHERE table2.z IS NOT NULL. Please get back with performance details. I hope this helps.

Regards,
Pradeep

Hi, Pradeep.
Thanks for the welcoming and for the suggestion. I tested it on the 300 rows tables and it took 55 seconds, compared to the ~63 needed to run it my way. So i worked up the courage and ran it on the big tables. Now, after all these hours, it finished processing the 73k rows.

thanks again for the tip :)
Bob.

[Motoma]

Hi, Pradeep.
Thanks for the welcoming and for the suggestion. I tested it on the 300 rows tables and it took 55 seconds, compared to the ~63 needed to run it my way. So i worked up the courage and ran it on the big tables. Now, after all these hours, it finished processing the 73k rows.

thanks again for the tip :)
Bob.

Setting up Indexes should speed this up dramatically.

[pradeep kaltari]

Hi, Pradeep.
Thanks for the welcoming and for the suggestion. I tested it on the 300 rows tables and it took 55 seconds, compared to the ~63 needed to run it my way. So i worked up the courage and ran it on the big tables. Now, after all these hours, it finished processing the 73k rows.

thanks again for the tip :)
Bob.

Hi Bob,
Sorry I missed out: adding Indexes to the searchable columns will definately speed up the process.

Regards,
Pradeep