By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,910 Members | 1,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,910 IT Pros & Developers. It's quick & easy.

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

P: n/a
This is what I tried:

Expand|Select|Wrap|Line Numbers
  1. update student_db.student set activities = (select i.activities from
  2. client.student c, student_db.student s where c.unique_key =
  3. s.unique_key);
  4. ERROR 1093 (HY000): You can't specify target table 'student' for update
  5. in FROM clause
  6. mysql>
  7.  
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

Jan 27 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<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.
Jan 27 '06 #2

P: n/a
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.


Jan 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.