CODEWITHSUNDEEP
sqlsql-server
Imad
Imad

Reputation: 7490

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'.

Upvotes: 0

Views: 436

Answers (3)

Alex Paul
Alex Paul

Reputation: 1

if there is no entry in the table update cannot be done try it after giving asingle entry it works

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15389

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

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

You need to ensure that that UPDATE isn't compiled until after you're actually created the column.

Put it in a separate context by using EXEC:

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 Table T1 
         SET C1 = 1')
END
GO

Upvotes: 5

Related Questions