I need to move column field values from one database table to another database table

[phillip.s.powell]

This is what I tried:

update student_db.student set activities = (select i.activities from
client.student c, student_db.student s where c.unique_key =
s.unique_key);
ERROR 1093 (HY000): You can't specify target table 'student' for update
in FROM clause
mysql>
 

I just need to move all of the contents in client.student.activities to
student_db.student.activities, how do I do that?

Thanx
Phil

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I just need to move all of the contents in client.student.activities to
student_db.student.activities, how do I do that?

MySQL versions 4.0 and later support an extension to SQL called multi-table
updates. You can do something analogous to a join, in an update statement.
This is not standard SQL, but imho it is so useful and sensible that it
should be!

Something like this should work (but I haven't tested it):

UPDATE student_db.student, client.student
SET student_db.student.activities = client.student.activities
WHERE student_db.student.unique_key = client.student.unique_key

See http://dev.mysql.com/doc/refman/5.0/en/update.html for more info.

Regards,
Bill K.

[phillip.s.powell]

That was perfect! I have never heard of this before, but thanx!

Phil

Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I just need to move all of the contents in client.student.activities to
student_db.student.activities, how do I do that?

MySQL versions 4.0 and later support an extension to SQL called multi-table
updates. You can do something analogous to a join, in an update statement.
This is not standard SQL, but imho it is so useful and sensible that it
should be!

Something like this should work (but I haven't tested it):

UPDATE student_db.student, client.student
SET student_db.student.activities = client.student.activities
WHERE student_db.student.unique_key = client.student.unique_key

See http://dev.mysql.com/doc/refman/5.0/en/update.html for more info.

Regards,
Bill K.