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

This is what I tried:

[CODE]
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>
[/CODE]

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

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

wrote in message
news:1138385724.042895.122130@g43g2000cwa.googlegroups.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.

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

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

Phil

Bill Karwin wrote:
> wrote in message
> news:1138385724.042895.122130@g43g2000cwa.googlegroups.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.