Solved

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

Posted on 2014-03-19
4
689 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 35

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 35

Expert Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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