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 have table1 and table2 in MySQL. Both have a primary auto_increment key id.

If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows inserted in to table1? Do they keep their old id values and generate conflicts when a row from table1 has the same id? Are new values generated by auto_increment? Does it depend on the storage engine or locking?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator.

If you try to insert a value of NULL or 0 or DEFAULT, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.

So, it's fine to INSERT INTO table1 SELECT * FROM table2 (by the way, you don't need the parentheses). This means that the id values in table2 will be copied verbatim, and table1 will not generate new values.

If you want table1 to generate new values, you can't do SELECT *. Either you use null or 0 for the id column:

INSERT INTO table1 SELECT 0, col1, col2, col3, ... FROM table2;

Or else you omit the column from both the INSERT statement's column list and the SELECT statement's select-list:

-- No id in either case:
INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3, ... FROM table2;

Before you ask, there is no syntax in SQL for "select * except for one column". You have to spell out the full list of column names you want to insert.

share|improve this answer
    
Isn't there an option that allows/disallows insert of 0 as a value? (Regarding your 2nd paragraph) –  Sascha Rambeaud Mar 10 '14 at 10:14
    
Yes, SQL_MODE=NO_AUTO_VALUE_ON_ZERO, otherwise we could never insert a literal zero value into an AI column. If you use this SQL mode, NULL still works to activate the AI generator, or omitting the column works too. –  Bill Karwin Mar 10 '14 at 16:09
    
@BillKarwin, Regarding your last paragraph, after getting the subresult select * from table, is there no way to operate on this subresult to remove its first column? –  Pacerier Feb 23 at 4:35
    
@Pacerier, if I understand correctly, you're asking if select * can mean select * except for the first column. The answer is no. select * always requests all the columns from that table. If you want a subset, you must spell out the columns you want. –  Bill Karwin Feb 24 at 1:58
    
@BillKarwin, Hmm, I was thinking of pivoting the table to do vertical where clauses, then pivoting it back e.g. select*from transpose(select*from(transpose(select*from table where Id=1))where col_name<>Id) or perhaps more succinctly as select*from table where Id=1 and $col<>Id, what do you think? –  Pacerier Feb 24 at 5:09

The id from the select will be the same value inserted into the table. This will result in an error if you're trying to duplicate existing rows.

Bill Karwin: Before you ask, there is no syntax in SQL for "select * except for one column".

This can be achieved with some creativity:

SET @sql = CONCAT('INSERT INTO <table> SELECT null, 
    ', (SELECT GROUP_CONCAT(COLUMN_NAME) 
    FROM information_schema.columns 
    WHERE table_schema = '<database>' 
    AND table_name = '<table>' 
    AND column_name NOT IN ('id')), ' 
from <table> WHERE id = <id>');  

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

This will result in the new row getting an auto incremented id instead of the id from the selected row.

share|improve this answer
1  
Clever, but to me, that counts as spelling out the full list of column names you want to insert. –  Bill Karwin May 30 '14 at 23:10
1  
Well it's mysql automatically spelling it out versus doing it manually. If you have tables with a lot of columns, it'll prevent you from missing one or misspelling a few. –  curmil May 30 '14 at 23:50

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.