add new column in table with value depending value of another column in same table

Question

I am new in Mysql . I want to add a column in table with value depending another column value in the same table. For example I have company_table as below :

fldId | companyName | date
--------------------------------
  1   | adeco       | 2012-01-12    
  2   | mic         | 2001-03-09
  3   | manpower    | Null
  4   | linar       | Null
  5   | dlank       | 1999-02-28

I want to add 'fldState' column in this table depending on value of 'date' column. For example if value of 'date' column is not Null then value of 'fldState' should be 1 and if it's Null then value of 'fldState' should be 2. like below

  fldId | companyName | date        | fldState
  --------------------------------------------
  1     | adeco       | 2012-01-12  | 1
  2     | mic         | 2001-03-09  | 1 
  3     | manpower    | Null        | 2
  4     | linar       | Null        | 2
  5     | dlank       | 1999-02-28  | 1

Answer 1

From your existing table, you need to create an ALTER statement so that you can add new column.

ALTER TABLE mytableName ADD fldState INT;

• Adding New Column from a table

After the statement has been successfully executed, you can now update all the records,

UPDATE  myTableName
SET     fldState = IF(date IS NULL, 2, 1)

Answer 2

To add new new column you can use following command

ALTER TABLE company_table ADD fldState;

If you want to add those column in your wishing place . As like if you want to add fldState after companyName , Then use as like following

ALTER TABLE company_table ADD fldState AFTER companyName;

IF you want to add Column as First Column, Then use aslike following

ALTER TABLE company_table ADD fldState FIRST;

if you don't use any thing more , Then as default it will be placed at last.

Now use as like following command to Copy Column data.

 UPDATE company_table SET fldState = IF(date IS NULL, 2, 1);