update tbl X using data from table Y

[JMPS]

Hello!

In table X I need to update every row in a column using data from columns in table Y.

Is there any clean way to do it in mySQL? Since UPDATE seems accept no joins, I can only imagine two solutions: Either I make a script to do the job column by column (one UPDATE command for each column), or I make an empty copy of the table that I want to modify, fill it with INSERT ... SELECT and drop the old table. Any of these solutions look very, very, very bad for big tables...

What am I missing?

Thanks,
jmps

 

[doddo]

Hi, did you get a response to this, i'm looking at the same problem?

cheers
Rich

 

[rubis]

Hi guys,

I've got the same problem now. Any suggestion??

update t1, t2
set t1.txt = t2.txt
where t1.id = t2.id;

I've tried this code in Access and it did work well but not for MySQL at all.

 

[gshirey]

I have the same delima. Please oh guru's answer us!

 

[Broccoli2]

Try this - it's not pretty, but might be what you need:

CREATE TABLE temptable
SELECT
tabletoupdate.ID,
tabletoupdate.nochangecol,
tableupdatefrom.changecol
FROM tabletoupdate
INNER JOIN tableupdatefrom ON tabletoupdate.ID=tableupdatefrom.ID;

DROP TABLE tabletoupdate;

RENAME TABLE temptable TO tabletoupdate;

-Rob