• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • 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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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