CODEWITHSUNDEEP
mysqlsql
Dushyant Joshi
Dushyant Joshi

Reputation: 3702

How to select column name in select statement

I want to copy/update data from Table A to Table B. Table B has some more additional columns. I have tried the following options.

1) `REPLACE INTO `B` (SHOW FIELDS FROM 'A') SELECT * FROM `A

2) `REPLACE INTO `B` 
     (SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` 
              WHERE `TABLE_SCHEMA`='test1' AND `TABLE_NAME`='A') SELECT * FROM `A

But it throws errors. Can you guys help me how to select names with select query?

UPDATE:

3) As suggested by Jerko,

I have two tables A(warehouse_id,long,lat) B(warehouse_id,long)

Applied the following statement.

SET @query = CONCAT('REPLACE INTO `A` (SELECT  ', 
                     (SELECT GROUP_CONCAT(CONCAT('`',column_name, '`')) 
                      FROM information_schema.columns 
                      WHERE  `TABLE_SCHEMA`='test2' AND `table_name` = 'A'), 
                   ' FROM  `B`)');


PREPARE stmt FROM @query;
EXECUTE stmt;

This gives me the error

"#1054 - Unknown column 'lat' in 'field list' "

Upvotes: 1

Views: 2565

Answers (4)

Jerko W. Tisler
Jerko W. Tisler

Reputation: 989

Actually there is a way

SET @query = CONCAT('REPLACE INTO `A` (', 
                  (SELECT GROUP_CONCAT(CONCAT('`',column_name, '`')) 
                  FROM information_schema.columns 
                  WHERE  `TABLE_SCHEMA`='test1' AND `table_name` = 'A'
                  AND column_name IN (SELECT column_name FROM information_schema.columns WHERE table_schema = 'test1' AND table_name='B')) ,
                  ') (SELECT  ', 
                 (SELECT GROUP_CONCAT(CONCAT('`',column_name, '`')) 
                  FROM information_schema.columns 
                  WHERE  `TABLE_SCHEMA`='test1' AND `table_name` = 'A'
                  AND column_name IN (SELECT column_name FROM information_schema.columns WHERE table_schema = 'test1' AND table_name='B')), 
               ' FROM  `B`)');


PREPARE stmt FROM @query;
EXECUTE stmt;

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

INSERT INTO B (field1,field2,...,fieldN) 
        SELECT (field1,field2,...,fieldN) FROM A

Upvotes: 1

davidethell
davidethell

Reputation: 12018

You can't do this dynamically in mysql like you are trying to do. MySQL expects your list of column names to be provided directly, not from a subquery.

If you want to do this dynamically you'll have to step back upstream to whatever language you are using to interact with MySQL such as PHP or Java.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Have you tried this?

insert into B(col1, . . ., coln)
    select col1, . . ., coln
    from A;

That is, list the fields from A in the select clause. List the corresponding columns for B in the insert column list.

If you need the list of columns, get them from INFORMATION_SCHEMA.COLUMNS and cut-and-paste into the query.

Upvotes: 2

Related Questions