INSERT Query for complete row expect id

Question

I tried to insert a complete row to a table. But the problem is, that I use the same IDs. Is there a easy way to insert a complete row expect the one value (ID)?

INSERT INTO new_table SELECT * FROM old_table WHERE q_value = 12345

I would not insert every single value, because there are hunderds of columns.

Thanks for your help in advance, Yab86

http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql — Hanky Panky, Jun 25 '14 at 07:14

score 1 · Answer 1 · answered Jun 25 '14 at 07:18

1

May be Something like this

You can do without getting column2

Insert Into new_table (Column1,Column3,Column4 ...)
Select Column1,Column3,Column4 ... From old_table 
WHERE q_value = 12345

answered Jun 25 '14 at 07:18

Vignesh Kumar A

27,863
13
63
115

hellcode · Answer 2 · 2014-06-25T07:46:18.647

1

If the first column in new_table is your auto_increment primary id, you could use this, but then you cannot use the asterisk and have to list all columns except the first one (0 will do the trick for your auto_increment, that means it will insert the incremented value of the autoindex):

INSERT INTO new_table
SELECT 0, column2, column3, ...
FROM old_table WHERE q_value = 12345

edited Jun 25 '14 at 07:46

answered Jun 25 '14 at 07:25

hellcode

2,678
1
17
21

score 1 · Answer 3 · answered Jun 25 '14 at 07:26

If ID is part of a unique key the only (good) way is to write out all the other columns, like this

insert into new_table 
values (col1, col2, col3)
select col1, col2, col3
  from old_table
 where q_value = 12345;

If ID isn't part of the unique key there might be some ways to do it in two queries (easier to write but perhaps not better)

insert into new_table 
select *
  from old_table
 where q_value = 12345;

update new_table
   set ID = null
 where q_value = 12345;

INSERT Query for complete row expect id

3 Answers3