ALTER TABLE ADD COLUMN and automatic COLUMNS list

Question

I am looking to add a new column to table using the suggestions provided here and here

In essence, I would like the (fields_in_orig_table) to be populated automatically, and not having to enter them manually (have many columns and this changes from table to table):

CREATE TABLE games_new LIKE games_orig;
ALTER TABLE games_new ADD COLUMN location varchar(256);
INSERT INTO games_new (fields_in_orig_table) SELECT * FROM games_orig;
RENAME TABLE games_orig TO games_old, games_new TO games_orig;
DROP TABLE games_old;

My thought goes around this:

CREATE TABLE games_new LIKE games_orig;
ALTER TABLE  games_new ADD COLUMN version varchar(256);
INSERT INTO  games_new 

          (SELECT CONCAT(GROUP_CONCAT(column_name ORDER BY ordinal_position 
           SEPARATOR " ,  "), " ") AS columns
           FROM information_schema.columns
           WHERE table_schema = 'games' AND table_name = 'games_orig' ) 

SELECT * FROM games_orig;
RENAME TABLE games_orig TO games_old, games_new TO games_orig;
DROP TABLE games_old;

This gives me syntax error (near the Select concat....).

The original syntax to get comma delimited column listings is:

SELECT CONCAT("'", GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR "', '"), "'") AS columns
FROM information_schema.columns
WHERE table_schema = 'db_name' AND table_name = 'tbl_name'

In my query, I have removed the extra quotes, as I figure my query does not require quotes as part of the column listing.

What am I doing wrong here? Who could help, please?

If you set up an SQL fiddle it might be easier for us to help — cameronjonesweb, Feb 10 '15 at 07:14

cameronjonesweb · Answer 1 · 2015-02-10T07:04:43.923

0

INSERT INTO  games_new 

VALUES (SELECT CONCAT....

edited Feb 10 '15 at 07:04

answered Feb 10 '15 at 06:59

cameronjonesweb

2,435
3
25
37

Thanks for quick response, still getting the same Error 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT CONCAT(GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR " , ' .....) – Stan Feb 10 '15 at 07:02

Rigel1121 · Answer 2 · 2015-02-10T07:15:20.970

When inserting values in the table use INSERT INTO tablename VALUES (fields) instead of INSERT INTO tablename SET(fields).

CREATE TABLE games_new LIKE games_orig;
ALTER TABLE  games_new ADD COLUMN version varchar(256);
INSERT INTO  games_new 
          VALUES
          (SELECT CONCAT(GROUP_CONCAT(column_name ORDER BY ordinal_position 
           SEPARATOR ' , '), ' ') AS columns
           FROM information_schema.columns
           WHERE table_schema = 'games' AND table_name = 'games_orig' ) 

SELECT * FROM games_orig;
RENAME TABLE games_orig TO games_old, games_new TO games_orig;
DROP TABLE games_old;

instead of double qoute, try using single quote only. See my edited answer. — Rigel1121, Feb 10 '15 at 07:15

score 0 · Answer 3 · answered Feb 10 '15 at 07:21

0

You haven't specified which column you want to insert into, because your nested query is returning only 1 value

INSERT INTO games_new (column_name_u_want_to_insert_value_into) 
SELECT   cast(concat(group_concat(column_name ORDER BY ordinal_position SEPARATOR " , "), " ") AS CHAR) AS columns
FROM     information_schema.columns 
WHERE    table_name = 'games_orig';

also, if you are running all the statements together, add semicolon(;) for the insert query as well

answered Feb 10 '15 at 07:21

Sachin More

1

Is the Fiddle I created sufficiently built out? – Stan Feb 10 '15 at 10:32
I have got the result now that your code provides! mysql> select * from games_new; +------+-----------+-------------+-----------------------------+ | id | game_name | year_issued | version | +------+-----------+-------------+-----------------------------+ | NULL | NULL | NULL | id, game_name, year_issued | +------+-----------+-------------+------------------- However - what I'd like is for the new table (games_new) to actually copy those actualy columns (ones that comes as the resulkt of the query), not to have the title of the columns listed in one cell. – Stan Feb 10 '15 at 13:44

ALTER TABLE ADD COLUMN and automatic COLUMNS list

3 Answers3