• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

How to insert from a select where fields don't match in MySQL?

Hi,

I want to create a table which is a copy of another tables data, but, this table is a log which will keep every change of every record that ever happens, as such I have added a new primary key and made the previous primary key just a regular integer value.

I want to select all data from one table and insert it in the other.
Both tables are identical except that the table I am inserting the data into has one extra field a primary key which I want to auto increment and does not exist in the table I am selecting from.

Naturally I get a MySQL error from the query below which would work fine if both tables where identical: #1136 - Column count doesn't match value count at row 1
INSERT `health4_mt_address_log` SELECT * FROM `health4_mt_address`;

Open in new window

0
Jeremy Leys
Asked:
Jeremy Leys
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
Then enumerate the fields:
INSERT INTO `health4_mt_address_log` (field1, field2,.., fieldn)
   SELECT field1, field2, ..., fieldn FROM `health4_mt_address`

Open in new window

HTH,
Dan
0
 
Cornelia YoderArtistCommented:
I asked a similar question some time back, and got a very clear excellent answer here:

http://www.experts-exchange.com/Database/MySQL/Q_28029891.html
0
 
Jeremy LeysTechnical LeadAuthor Commented:
That works perfectly, looking at it, I don't know why I didn't think of it, dumb moment I guess :)

Thank you so much for your help!
0
 
Dan CraciunIT ConsultantCommented:
Glad I could help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now