Mysql - How To Alter Table Column To Add Two Column

I want to add two column sno bigint and prefix varchar 2 as id

for example P(prefix) + 12(sno) = P12.

So I tried this query

alter table table_name id as prefix + sno;

But I got error. I dont even know the correct syntax.

Somebody help me to get correct query

Try asking such questions on mysql forum or just read the manual. You will learn a lot about SQL.

You can use CDbCommand class to do it.




// create instance of CDbCommand class

$command = Yii::app()->db->createCommand();


// create query and run it to select appropriate number from your table

$no = $command->select('sno')

              ->from('table_name')

              ->order('sno desc')

              ->queryScalar();


// merge the prefix and the number 

$col_name = 'P'.$no;


// if you want to rename the current column use this code

$command->renameColumn('table_name', 'id', $col_name);


// if you want to add new column use this code

$command->addColumn('table_name', $col_name, 'varchar(20)');