How do i add a column in a sql table only if it not exists
something like "ALTER TABLE `Cart` CHANGE IF EXISTS `EmailCust`
`EmailCust` VARCHAR(254)" (sql 4.0) that i shouldn get a error

---

Request for Question Clarification by palitoy-ga on 31 Aug 2005 01:24 PDT

I am a little unsure as to what you require, can you clarify your
question a little?

Are you seeking to only add data to a column if that data does not
already exist?  For instance only add an email address if it does not
already exist.  If this is your problem, your solution should lie in
the UNIQUE command.

I would be happy to provide you with an answer discussing how to use
this command, just let me know by responding to this clarification.

palitoy-ga

---

Clarification of Question by isaac123456789-ga on 31 Aug 2005 13:18 PDT

hi palitoy-ga
Thanks for your response.

my question is to add a new column if the "column" is not already
exist (not on the data inside the existing column)
that i shouldn get a sql error

Thanks

---

Request for Question Clarification by hammer-ga on 31 Aug 2005 14:07 PDT

Isaac123456789-ga,

You would likely do this outside of the ALTER TABLE statement. For
example, if you are working in PHP, you might attempt a SELECT on the
column and check for an error before performing the ALTER TABLE.

If you are interested in this type of solution, please let us know
what language you are using.

Note to researchers: If Customer responds, feel free to provide an
answer. I may not be able to get back to this question.

- Hammer

---

Clarification of Question by isaac123456789-ga on 31 Aug 2005 16:27 PDT

in a perl script i know how to do it but i need in sql

---

Clarification of Question by isaac123456789-ga on 14 Sep 2005 15:19 PDT

I need a solution for mysql 4.0

You have a table, and you only want to update it with data if there is
already data present.  If not, you don't want to add new data?

isaac123456789-ga,

Palitoy-ga's solution is specific to SQL Server, which is a particular
Microsoft database package. The technique uses syntax which is not
part of the SQL standard itself, but is specific to Microsoft's
implementation, called T-SQL. MySQL does not use T-SQL and does not
have object_id. I don't believe that SQL alone offers a solution to
your problem. You can, in your code, attempt the operation and test
for the returned mySQL error so you can respond appropriately.

- Hammer

Here's the answer:

1. Check a property on the column. If it returns null, then the column
doens't exist:

IF columnproperty (object_id('MyTableName'), 'MyColumnName', 'AllowsNull') IS NULL
BEGIN
ALTER TABLE dbo.MyTableName ADD
	MyColumnName varchar(100) NULL

END

hi francescosydney-ga 
this answer is gone work onlt on sql server and i need for mysql
thanks anyway

Hi,

Maybe this is now a real answer 
but I think you are searching in the difficult way.

These are simple ways;
the two scenario in my head about your question.

First; You could upgrade your db to mysql 5.0; so you could easily use
these new features; "If Then" clause, "Stored Procedures" and also use
"Information_Schema Tables".

After upgrade, the job is writing a script that, takes the column
names from information schema table per table, and by If Clauses try
to Alter them in a sp.

My second suggestion; exract datas from "show tables" to a txt file.
Prepare it as "show columns table_example_name" per line. 
Try to execute sql sentences line by line and import the result a
table, with the table's name.
Then prepare an other batch file for your "Alter clause" with using
the column name data in the table. Prepare your clause what you wish.
Execute your last batch file.