mysql statement for adding multiple columns whether they exist or not

Question

I'm trying to write an update mysql statement for a software program. Currently this is what I have for the statment, but I don't now how to add multiple columns to update.

This code currently adds the column whether it's there or not.

/*delimiter '//'
CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME=`top_status` AND TABLE_NAME='categories'
)
THEN
ALTER TABLE `categories`
ADD COLUMN `top_status` tinyint(1) NOT NULL default '1';
END IF;
END;
delimiter ';'
CALL addcol();
DROP PROCEDURE addcol;*/

Can someone supply me with the correct statement to get something like this to work...ie. WHERE COLUMN_NAME = column1, column2 etc..

I've tried all kinds of variations and all of them return an error in phpmyadmin except the above.

Ganesh RJ · Accepted Answer · 2014-01-14 07:26:58Z

6

Use ALTER TABLE statement to add multiple columns to a table:

ALTER TABLE TableName
ADD (column1 col_definition,
column2 col_definition,
column3 col_definition,
....      ......
....      ......
columnN col_definition);

edited Jan 14, 2014 at 7:26

Ganesh RJ

9422 gold badges19 silver badges31 bronze badges

answered Mar 6, 2013 at 8:28

Amir Khalid

711 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hammerite · Accepted Answer · 2011-10-14 23:46:56Z

0

Surely you want to individually test for the existence of the new columns, and add each one individually if it doesn't already exist?

If you want the INFORMATION_SCHEMA query to test for the existence of any of the columns column1, column2 etc. you can use an IN statement. If you want to test whether they all exist then you can use IN and instead of checking whether the resultset is empty, select a COUNT(*) and check whether the count returned is the expected number (the number of columns involved).

answered Oct 14, 2011 at 23:46

Hammerite

22.5k6 gold badges76 silver badges103 bronze badges

7 Comments

sven30 Over a year ago

is my method even the correct way to do this? What I'm trying to do is setup a statement I can use for many different databases that are outdated at different version with different data/columns. Some db's have the columns, some don't, some have all the latest columns. But I just want one statement to run against each db to basically make all db's the same and latest build. So, is this the best method or is there a better way? Also, is below post the way you would go about adding your statements?

sven30 Over a year ago

/*delimiter '//' CREATE PROCEDURE addcol() BEGIN IF NOT EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME IN (top_status, 'categories') AND TABLE_NAME='categories' ) THEN ALTER TABLE categories` ADD COLUMN (top_status tinyint(1) NOT NULL default '1',categories tinyint(1) NOT NULL default '1'); END IF; END; delimiter ';' CALL addcol(); DROP PROCEDURE addcol;*/ This doesn't work btw.

Hammerite Over a year ago

That's not the correct syntax for ALTER TABLE; the correct syntax is

ALTER TABLE categories ADD COLUMN top_status tinyint(1) NOT NULL default '1', ADD COLUMN categories tinyint(1) NOT NULL default '1'

Hammerite Over a year ago

The IF statement in your comment tests whether the table has at least one of the columns top_status and categories. If it has one, but not the other, then the IF statement is satisfied and you will run into an error when you attempt to add the column that is already present. So, if the table might have some columns but not others, you need to test for the presence of each column individually and deal with each one that isn't there. This means multiple IF statements.

sven30 Over a year ago

ok, this is what I have now and it's still not updating both columns. No errors, but no adding either. /*delimiter '//' CREATE PROCEDURE addcol() BEGIN IF NOT EXISTS( SELECT COUNT(*) FROM information_schema.COLUMNS WHERE COLUMN_NAME IN (categories_status, top_status') AND TABLE_NAME='categories' ) THEN ALTER TABLE categories ADD COLUMN categories_status int(1) unsigned NOT NULL default '1',top_status tinyint(1) NOT NULL default '1'; END IF; END; delimiter ';' CALL addcol(); DROP PROCEDURE addcol;*/

|

Collectives™ on Stack Overflow

mysql statement for adding multiple columns whether they exist or not

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related