Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to select and insert data from an old table to the new table:

CREATE TABLE test2 LIKE test1
INSERT INTO test2 SELECT timestamp, datenum FROM test1 WHERE datenum>=23 and datenum<=1000000 ON DUPLICATE KEY UPDATE datenum=VALUES(datenum)

Also I have and id column in the test2 and test1, but I would like to start the id from 1. If I use the query above I'm receiving the following error

ERROR 1136: Column count doesn't match value count at row 1


INSERT INTO test2 SELECT * FROM test1 WHERE datenum>=23 and datenum<=1000000 ON DUPLICATE KEY UPDATE datenum=VALUES(datenum)

In this case the id start from 3239.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

I believe id is autoincrement field, and you want this value to start with 1 in new table. You can specify column list in your first query, so it won't fail :

INSERT INTO test2 (`timestamp`, datenum) 
SELECT `timestamp`, datenum 
FROM test1 WHERE datenum>=23 and datenum<=1000000 
share|improve this answer
    
I don't think ON DUPLICATE KEY UPDATE datenum=VALUES(datenum) is necessary since test2 is an empty table. +1 for this answer, anyway !!! –  RolandoMySQLDBA Jun 18 '12 at 18:05
    
@ RolandoMySQLDBA : Agree, I should have read the question carefully, not just pasted... Fixing –  a1ex07 Jun 18 '12 at 18:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.