Mysql: SELECT * FROM .... without one field

Question

For duplicating an entry I would like to use the following syntax:

insert into TABLE select * from TABLE where ...

However, as the first column is an auto-increment primary key, this value must be different. My workaround was to specifiy all fields in the select query instead of using the asterisk and then leaving the primary key field blank. Since my table has more than thirty fields that unfortunately keep changing, I am looking for a solution that I can implement in a script and that does not need to be modified when the table structure changes. Any ideas? Thank you very much!

Unfortunately, there's not really another way. See my question [SELECT * EXCEPT](http://stackoverflow.com/questions/413819/select-except) that's in a similar vein. — Glen Solsberry, Dec 21 '11 at 13:32

score 4 · Answer 1 · answered Dec 21 '11 at 13:39

Example:

mysql> create database dbase;
Query OK, 1 row affected (0.00 sec)

mysql> use dbase;
Database changed
mysql> create table tbl ( id integer not null primary key auto_increment, field2 integer, field3 integer );
Query OK, 0 rows affected (0.04 sec)

mysql> SET @sql = CONCAT ( 'SELECT ' , ( SELECT REPLACE ( GROUP_CONCAT( COLUMN_NAME ) , 'id,' , '' ) FROM INFORMATION_SCHEMA.COLUMNS WHERE T
ABLE_NAME = 'tbl' AND TABLE_SCHEMA = 'dbase'), ' FROM tbl');
Query OK, 0 rows affected (0.01 sec)

mysql> select @sql;
+-------------------------------+
| @sql                          |
+-------------------------------+
| SELECT field2,field3 FROM tbl |
+-------------------------------+
1 row in set (0.00 sec)

mysql>

Now you have only to prepare and execute ..

mysql> prepare stmt from @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
Empty set (0.00 sec)

No rows returned because don't have rows inserted in table.

And now inserting and executing again ..

mysql> insert into tbl ( field2, field3 ) values ( 1 , 2 ) , ( 3 , 4 ) ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> execute stmt;
+--------+--------+
| field2 | field3 |
+--------+--------+
|      1 |      2 |
|      3 |      4 |
+--------+--------+
2 rows in set (0.00 sec)

Return all table fields except 'ID' of specified in @sql

score 3 · Accepted Answer · answered Dec 21 '11 at 13:38

MySQL does not provide a way to eliminate fields like this. However, if you know you'll always want all the fields but the primary key, then there's really not much overhead to fetching the primary key as well. It will be much easier to exclude the primary key in your code.

If it's really important to get this, you could execute DESCRIBE TABLE to get a list of columns and then construct your query. However, this is likely just to make things a lot more complicated. I would advise just ignoring the primary key.

Mysql: SELECT * FROM .... without one field

2 Answers2