Merging Two Tables with Data

[atheistrical]

I have two different tables with data in them namely

Table I (main_table)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
id | batch_no | name | type | ......................etc columns
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 | 001 | ABC | 2 | ................
2 | 001 | BCA | 0 | ................
3 | 002 | ACB | 1 | ................
4 | 002 | CAB | 2 | ................
-------------and so on------------------------


Table II (other_table)
~~~~~~~~~~~
batch_no | type
~~~~~~~~~~~
003 | 3
001 | 1
002 | 0
006 | 2
----so-on------



The batch_no in Table I is not unique, while batch_no in Table II is unique column

Now, I wanted to update the columns of Table I with the corresponding data from Table II

I tried :

UPDATE Table_I AS t1
SET type=(SELECT type FROM Table_II AS t2)
WHERE t1.batch_no=t2.batch_no



Doesn't work!! Any suggestions?

[Old Pedant]

http://dev.mysql.com/doc/refman/5.1/en/update.html

Look for the text starting with "You can also perform UPDATE operations covering multiple tables"

[lrcroft1]

Select Table I (main_table)
UNION
Table II (other_table);

[Old Pedant]

No, lrcroft, not close. He doesn't want to do a SELECT; he wants an UPDATE. And he doesn't need a UNION.

[atheistrical]

Thank you Old Pedant. As always, your post was helpful. However, due to the size of the data (i.e. no of rows in both the tables exceeding 50,000), I resorted to a different method and here is how I did it.

First, I created a Temporary Table


CREATE TABLE new_table_I AS
SELECT t1.id,t1.batch_no,t1.name,t2.type
FROM table_I AS t1
LEFT JOIN table_II AS t2 ON t1.batch_no=t2.batch_no


...and deleted table_I and table_II,
and finally renamed new_table_I to table_I

What do you think? Could have been easier??

[Old Pedant]

Looks workable. I'd benchmark it both ways--like this and with the update--and choose the faster.

One problem with this solution: table_I is inaccessible to other users/threads/connections during the time you do the delete and rename. If that's acceptable, then ignore this comment.

[manickarajg]

UPDATE Table_I AS a, Table_II AS b SET a.type=b.type WHERE a.batch_no=b.batch_no;