CODEWITHSUNDEEP
sqlite
Joe
Joe

Reputation: 447

Add column to a table if not exist

I have a problem to add a new column to a SQLITE table if this column is not already exist.

I have try with this code but i don’t know why it wont execute:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mynewcolumn')
BEGIN
    ALTER TABLE mytable ADD COLUMN mynewcolumn TEXT    
END

I get an Exception error :

error: near "IF": syntax error

Upvotes: 1

Views: 5970

Answers (2)

Joe
Joe

Reputation: 447

This is the solution i select :

1- I do PRAGMA table_info :

pragma table_info(MyTable)

This command gives all the informations about all the columns of the table. each row correspand to the information of a column. This commande return an output table with 4 columns : cid, name, type, notnull, dft value, pk

2- I read all the rows from "PRAGMA table_info (MyTable)", and i compare the column "name" with the name of the column i want to check if exist.

3- If Column exist then i dont do anything

4- but if the column doen't exist then, here i add the column to my table using this commade :

ALTER TABLE MyTable ADD COLUMN NewColumn TEXT;

this is work for me, and do the job correctly.

Upvotes: 3

CL.
CL.

Reputation: 180250

To test whether a column exists, execute PRAGMA table_info and check if the column name appears in the result.

Upvotes: 1

Related Questions