Add a new column

[jatashankar]

Hi,

I have one query related to the mysql adding column query is-

*/

alter table tbl_oppourtunities add column New_Oppourtunities as (lower(product))

/*

if above query we have written then its throwing error like this---

Error

SQL query:

ALTER TABLE tbl_oppourtunities ADD COLUMN New_Oppourtunities AS (
lower( product )
)

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as (lower(product))' at line 1

-----------
IN ABOVE-

'Product' is a Column name And 'New_Oppourtunities ' column name that we want to add in a table 'tbl_oppourtunitiestable'

So please reply me with your best ideas ASAP.

Thanks

Best regard
Jatashankar patel

[urtrivedi]

ALTER TABLE tbl_oppourtunities ADD col1 varchar(20), add col2 varchar(25);

[rch1231]

Hello,

If I am reading your post correctly the product field already exists and New_Oppourtunities is the new field you want to add. Your problem is the AS entry. AS is used to rename an output column heading when a query is run against the table and is not an acceptable parameter for an ALTER TABLE query.

Add a Column
ALTER TABLE table1 ADD COLUMN col_5 MEDIUMTEXT
Change a Column Type
ALTER TABLE table1 CHANGE col_3 col_3 TEXT
Remove a Column
ALTER TABLE table1 DROP COLUMN col_4
Rename a Column
ALTER TABLE table1 CHANGE col_3 col_4 TEXT
Rename a TABLE
ALTER TABLE table1 RENAME TO table2
Add an Index
CREATE INDEX indexname ADD INDEX (col_1)

[jatashankar]

ThankS for quick reply,

But i want output of function based column into a new column. because that will be help to solves the issue of function based indexes.

So please reply me ASAP.

Thanks

Jatashankar patel

Hello,

If I am reading your post correctly the product field already exists and New_Oppourtunities is the new field you want to add. Your problem is the AS entry. AS is used to rename an output column heading when a query is run against the table and is not an acceptable parameter for an ALTER TABLE query.

Add a Column
ALTER TABLE table1 ADD COLUMN col_5 MEDIUMTEXT
Change a Column Type
ALTER TABLE table1 CHANGE col_3 col_3 TEXT
Remove a Column
ALTER TABLE table1 DROP COLUMN col_4
Rename a Column
ALTER TABLE table1 CHANGE col_3 col_4 TEXT
Rename a TABLE
ALTER TABLE table1 RENAME TO table2
Add an Index
CREATE INDEX indexname ADD INDEX (col_1)

[rch1231]

I am not sure what you mean by your latest post. Are you trying to create a custom field name based on something that is input by the user? When you add the new column to the table a field to hold that piece of information is added to every record. An example would be if you had a table of names with addresses and decided that you needed to add an Second address line to every record.

Prior to altering the table it might look like:

ID             int     11 primary key, auto-increment, not null
Company_Name   varchar 55 Not Null
Address        varchar 55
City           varchar 55
State          varchar  2
Zip            varchar 10

You would use alter table to add an additional field to hold the second address line:

ALTER TABLE table1 ADD COLUMN Address2 varchar(55)

Your table would now look like this:

ID             int     11 primary key, auto-increment, not null
Company_Name   varchar 55 Not Null
Address        varchar 55
City           varchar 55
State          varchar  2
Zip            varchar 10
Address2       varchar 55

If you are looking at add a record or updating the contents of an individual record then you would use INSERT or UPDATE queries.