Imad Imad -4 years ago 85
SQL Question

Alter then update causes an error

I have simple script:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'T1' AND column_name = 'C1')
BEGIN
ALTER Table T1
ADD C1 BIT NOT NULL CONSTRAINT DF_T1_C1 DEFAULT 0

UPDATE Table T1
SET C1 = 1
END
GO


I am getting error


Incorrect syntax near the keyword 'Table'.


I tried this solution but it didn't update column value. I came accross this but I think this is not my case as I don't want to catch exceptions or do any transaction. Do I have easy option to do this?

Putting GO seperator didn't help too.

As Joe Taras pointed out, I have changed my script but now getting error


Invalid column name 'C1'.

Answer Source

Your row:

UPDATE Table T1 SET C1 = 1

has wrong because you have specified table keyword.

The correct syntax is:

UPDATE T1 SET C1 = 1

EDIT 1

Rewrite your script as follow, so after GO separator you'll update your field, so you are sure the DDL has taken by DBMS:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.Columns
               where table_name = 'T1' AND column_name = 'C1')
BEGIN
    ALTER Table T1
    ADD C1 BIT NOT NULL CONSTRAINT DF_T1_C1 DEFAULT 0    
END
GO

UPDATE T1 SET C1 = 1

EDIT 2

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.Columns
               where table_name = 'T1' AND column_name = 'C1')
BEGIN
    ALTER Table T1
    ADD C1 BIT NOT NULL CONSTRAINT DF_T1_C1 DEFAULT 0  

    EXEC('UPDATE T1 SET C1 = 1')  
END
GO
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download