CODEWITHSUNDEEP
sqlsql-server-2008
coder
coder

Reputation: 6233

How do you reference new columns immediately after they are created in your SQL script

How do I need to write my SQL script to ensure my new column is visible on following lines after it is created.

This is the general form of my SQL:

BEGIN TRANSACTION

  if (not exists(select 1 from THIS_TABLE))
      BEGIN
        ALTER TABLE THIS_TABLE add THIS_COLUMN int
      END
COMMIT

BEGIN TRANSACTION

    IF (NOT EXISTS (SELECT 1 FROM THIS_TABLE
            WHERE THIS_COLUMN = 1))
    BEGIN
            UPDATE THIS_TABLE SET THIS_COLUMN = 1 
    END

COMMIT

This is the error I'm getting:

Invalid column name 'THIS_COLUMN'.

on this line:

        IF (NOT EXISTS (SELECT 1 FROM THIS_TABLE
            WHERE THIS_COLUMN = 1))

Upvotes: 2

Views: 2034

Answers (2)

Jose Rui Santos
Jose Rui Santos

Reputation: 15319

What Andomar said is correct, you need to use the go keyword. However, the big problem is that your logic looks wrong. Let me go through each use case:

If THIS_TABLE is not empty

If the table is not empty, the if below returns false and you will never add the new column.

  if (not exists(select 1 from THIS_TABLE))
          BEGIN
            ALTER TABLE THIS_TABLE add THIS_COLUMN int
          END

Then, the next script obviously fails, because there is no such column THIS_COLUMN:

IF (NOT EXISTS (SELECT 1 FROM THIS_TABLE
            WHERE THIS_COLUMN = 1))

If THIS_TABLE is empty

If the table is empty, the column is added:

  if (not exists(select 1 from THIS_TABLE))
          BEGIN
            ALTER TABLE THIS_TABLE add THIS_COLUMN int
          END

But then the next if will always be true and the update statement will affect zero rows (because table is empty).

IF (NOT EXISTS (SELECT 1 FROM THIS_TABLE
            WHERE THIS_COLUMN = 1))
BEGIN
        UPDATE THIS_TABLE SET THIS_COLUMN = 1 
END

Upvotes: 1

Andomar
Andomar

Reputation: 238276

The column has to be created before a query that uses it can be parsed. You can accomplish this by putting the update in a different batch, using the "go" keyword:

alter table t1 add c1 int
go
update t1 set c1 = 1

Or by running the second transaction as dynamic SQL:

alter table t1 add c1 int
exec ('update t1 set c1 = 1')

Upvotes: 8

Related Questions