select column names from table 2, and for this column names, set all the values to 0 from table1

Question

Edit:

I have a table: Say TABLE1, and this is the data:

--------------------------
COL1 | COL2 | COL3 | COL4
--------------------------
 1   |  10  | 100  | 1000
 1   |  10  | 100  | 1000
 1   |  10  | 100  | 1000
--------------------------

and say TABLE2,

---------------------------
ID | COL_NAMES
---------------------------
1  | COL3
2  | COL4
---------------------------

Now what I want do is select column names from table 2, and for this column names, set all the values to 0 from table1, This should be end result:

--------------------------
COL1 | COL2 | COL3 | COL4
--------------------------
 1   |  10  |   0  |    0
 1   |  10  |   0  |    0
 1   |  10  |   0  |    0
--------------------------

How do I write the query?

Answer 1

If you want to SELECT on Table1 you can do something like this:

SELECT CONCAT('SELECT ', GROUP_CONCAT(c.COLUMN_NAME),',',
(SELECT GROUP_CONCAT(0,' as ',t2.COL_NAMES) FROM table2 t2),' FROM table1;')
INTO @query
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'table1'
AND c.COLUMN_NAME not in (select COL_NAMES from table2)
ORDER BY c.ORDINAL_POSITION;

PREPARE stmt FROM @query;
EXECUTE stmt;

SELECT Query

and if you want to update table1 you can use this query for update it:

SELECT CONCAT('UPDATE TABLE1 SET ', GROUP_CONCAT(c.COLUMN_NAME, ' = ', 0) )
INTO @query
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'table1'
AND c.COLUMN_NAME in (select COL_NAMES from table2)
ORDER BY c.ORDINAL_POSITION;

PREPARE stmt FROM @query;
EXECUTE stmt;

UPDATE Query