0

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

Community
  • 1
  • 1
Imad
  • 7,126
  • 12
  • 55
  • 112
  • New error is because first the batch is *compiled* and then it's executed. At the point where it's trying to *compile* the `UPDATE` statement, no such column exists. – Damien_The_Unbeliever Feb 28 '17 at 10:21
  • can you not just change the default value as I have posted in my solution? then you don't even require the update?! – Tanner Feb 28 '17 at 10:29

3 Answers3

4

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
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • +1 `You need to ensure that that UPDATE isn't compiled until after you're actually created the column`. Perfect. – Imad Feb 28 '17 at 10:24
3

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
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
0

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

Alex Paul
  • 1