Copy MySQL columns from one table to another

Question

I'd like to copy a number of rows from one table to another within phpmyadmin. The table i'm copying from is the profile table. The table user i'm copying to already exists but the columns do not. I'm attempting the following command in the SQL tab of phpmyadmin.

INSERT INTO user 
   (profileImage, 
   skypeName, 
   facebookProfile, 
   twitterProfile, 
   reputation, 
   genderPreference, 
   agePreference, 
   fluentLanguage, 
   desiredLanguage) 

(SELECT profileImage, skypeName, facebookProfile, twitterProfile, reputation, genderPreference, agePreference, fluentLanguage, desiredLanguage FROM profile)

For some reason this isn't working out for me. I'm getting an error:

#1054 - Unknown column 'profileImage' in 'field list'

profileImage exists in the profile table, i.e. it is the name of one of my fields

Does anyone know what the problem might be?

Have you tried prefixing your table with your database name? INSERT INTO db.tbl and SELECT FROM db2.tbl ? — Kermit
– Kermit, Commented Aug 16, 2012 at 14:22
@RickKuipers Apologies, that was mistyped by me - that's not the problem, edited now — garethdn
– garethdn, Commented Aug 16, 2012 at 14:24
I think you answered the question yourself: the table exists, but the columns do not. Add the columns manually and run the query again. — Dirk McQuickly
– Dirk McQuickly, Commented Aug 16, 2012 at 14:25

Grim... · Accepted Answer · 2012-09-20 09:02:00Z

1

If the error is #1054 - Unknown column 'proileImage' in 'field list' then, simply, that column doesn't exist in either (or both) your profile or your user table.

Just run the select part of the query, ie. SELECT profileImage, skypeName, facebookProfile, twitterProfile, reputation, genderPreference, agePreference, fluentLanguage, desiredLanguage FROM profile

Does that work?

If so, are you sure profileImage exists in the user table?

edited Sep 20, 2012 at 9:02

answered Aug 16, 2012 at 14:25

Grim...

17k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

garethdn Over a year ago

When running your query i get MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0003 sec ). profileImage does not exist in the user table, i thought my SQL query would create the those columns when copying them from the profile table?

Grim... Over a year ago

Ah no, not at all. You'll have to create those columns first.

garethdn Over a year ago

Oh right, perhaps i was expecting too much from MySQL! So if i had 100 columns in table X that i wanted to copy to table Y, i'd have to create all those columns in table Y before i could copy the data from those columns in table X?

Grim... Over a year ago

Yes. I don't think you can do so programmatically in MySQL itself (but it's perhaps worth a new question), but you could certainly do so with php.

user1450789 · Accepted Answer · 2012-12-29 08:04:05Z

0

You can copy the structure of the table 1 to table 2 in phpmyadmin and can then run query to copy certain rows from table 1 to table 2.

answered Dec 29, 2012 at 8:04

user1450789

Collectives™ on Stack Overflow

Copy MySQL columns from one table to another

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related