SQL; how to skip 'ALTER TABLE' if the columns already exists

Question

So, I don't want to ALTER my table if it's already altered previously. In my WPF app, I got a button for creating new empty table, then with another button I'm ALTERing the table, in that process I'm adding new columns to the empty table. After that I'm inserting data to the columns. Now my problem is, when I click that button again, it wants to alter the table again, before the instert, but throwing an error since the columns already exists.

What I want is to skip the "alter table" when the columns already exists. This is my base code which works for one click:

 public bool updateTable(string tableNamee, string question, string Atype)
        {
            try
            {

                string query = $"ALTER TABLE appdb.{tableNamee} ADD question VARCHAR(100) NOT NULL AFTER questionID, ADD Atype VARCHAR(20) NOT NULL AFTER question; ";

                string query2 = $"INSERT INTO appdb.{tableNamee} (question, Atype) VALUES('{question}','{Atype}'); ";

            etc

This is what I tried:

 public bool updateTable(string tableNamee, string question, string Atype)
        {
            try
            {

                string q = $"IF NOT EXISTS( SELECT NULL FROM appdb.{tableNamee} WHERE table_name = {tableNamee} AND table_schema = appdb " +
                $"AND column_name = question, Atype)  THEN ALTER TABLE appdb.{tableNamee} ADD question VARCHAR(100) NOT NULL AFTER questionID, ADD Atype VARCHAR(20) NOT NULL AFTER question; END IF; ";


                string query2 = $"INSERT INTO appdb.{tableNamee} (question, Atype) VALUES('{question}','{Atype}'); ";

           etc

But I'm getting the following error: "You have an error in your SQL syntax" I tired to combine the code in many variations but I can't see what is the problem in syntax. Can someone help me with this?

Have a look [here](https://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-a-sql-server-table) — Muhammad Hannan, Aug 06 '19 at 12:00
I recommend having your C# application query the information schema table to obtain the current columns for your table. Then, only honor the `ALTER TABLE` command to add a new column if it does not already appear in the list. — Tim Biegeleisen, Aug 06 '19 at 12:00
Possible duplicate of [How to get database structure in MySQL via query](https://stackoverflow.com/questions/898688/how-to-get-database-structure-in-mysql-via-query) — Cleptus, Aug 06 '19 at 12:01
You should not check for column data but check for table structure — Cleptus, Aug 06 '19 at 12:02
@Amature24 For MySQL check [this](https://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql) — Muhammad Hannan, Aug 06 '19 at 12:05
`IF NOT EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{tableNamee}' AND table_schema = 'appdb' AND column_name = 'question')` — Sebastian Siemens, Aug 06 '19 at 12:08
Or just `SHOW COLUMNS FROM table_name` you can also filter it. Check [mysql show columns](https://dev.mysql.com/doc/refman/8.0/en/show-columns.html) and [extended show statement](https://dev.mysql.com/doc/refman/8.0/en/extended-show.html) — Cleptus, Aug 06 '19 at 12:14
What is the **exact** query you are executing? If you copy and paste that query into MySQL Workbench does it work there? — mjwills, Aug 06 '19 at 12:16
@mjwills I'm executing both queries presented here. And yes, the first code sample works perfectly, but if I click on the button for the second time it throws the error that the columns already exists since they are created on first click. So practically I don't want to execute the first query (alter table) when the columns exists. — Amateur24, Aug 06 '19 at 12:21
What did you mean by `You have an error in your SQL syntax` then? — mjwills, Aug 06 '19 at 12:22

score 0 · Answer 1 · answered Aug 06 '19 at 13:56

0

As @tim-biegeleisen wrote: Check for existence first.
As @bradbury9 wrote: Check the schema and not the data.

Call a query like this:

SHOW COLUMNS FROM 'tablename' LIKE 'fieldname';
or this:

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'tablename' AND COLUMN_NAME = 'fieldname'

Then depending on the result call the ALTER TABLE... query or not.

answered Aug 06 '19 at 13:56

LosManos

7,195
6
56
107

string q = $"IF COL_LENGTH('appdb.{tableNamee}', 'question') IS NULL BEGIN ALTER TABLE appdb.{tableNamee} ADD question VARCHAR(100) NOT NULL AFTER questionID, ADD Atype VARCHAR(20) NOT NULL AFTER question END; "; What's possible wrong here? Again giving syntax error... – Amateur24 Aug 06 '19 at 14:14
Don't know. Set a breakpoint and see what `q` contains. Then paste the string into your sql tool or [SqlFiddle](http://sqlfiddle.com/) and see if you get more info. – LosManos Aug 06 '19 at 14:29

SQL; how to skip 'ALTER TABLE' if the columns already exists

1 Answers1