0

I have a table1 with fluctuating number of columns. (colname1,colname2,colname3...colnamen)

I want to :

ALTER TABLE table2 CHANGE COLUMN `columns` `columns` ENUM('colname1','colname2','colname3' .... '') ...

each time the table1 gets changed in the structure

I know how to get the column names in a table with:

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema=

But how can I now get them in a string into the ALTER TABLE ?

  • The term you're looking for is "dynamic SQL". However, I think you're doing all this wrong. – mustaccio Feb 1 at 3:08
  • @mustaccio thank you very much for the hint. but how can I transform the rows of a table into a array or a string ? – Max Muster Feb 1 at 3:11
  • 1
    SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR '') FROM information_schema.columns WHERE table_schema= The result would be a string of all columnames. But i really don't understand what you want to do – nbk Feb 1 at 13:15
1

@nbk had given what you want - atleast what you made us to understand.

But...

Think of re-designing your whole job. Do not go by your fluctuating number of columns. There are better ways. json is one. Transactional database is what is been used in lot of projects. Those are the right methods to store / retrieve than having variable number of columns.

|improve this answer|||||
  • Fine. If you explain more, we shall help you improve / re-design. – Natarajan N Napoleon Feb 9 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.