Solved

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

Posted on 2014-03-19
4
687 Views
Last Modified: 2014-03-19
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
Comment
Question by:jwleys
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39940812
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
 
LVL 27

Expert Comment

by:yodercm
ID: 39940819
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
 

Author Closing Comment

by:jwleys
ID: 39940853
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39940861
Glad I could help!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question