1

I must add a Not Nullable column, but I don't have a known value for default so I create the column as Nullable, after that I update all rows and then change the column to be Not Nullable.

Running the following script finished with success.

BEGIN
    ALTER TABLE driver ADD DriverLocationId INT NULL
    EXEC('UPDATE driver 
    SET DriverLocationId = (SELECT TOP(1) CountryId FROM Country WHERE CountryCode IN (''USA'',''CAN'', ''MEX''))')
    ALTER TABLE driver ALTER COLUMN DriverLocationId INT NOT NULL   
END

And the following is failing.

BEGIN
    ALTER TABLE driver ADD DriverLocationId INT NULL
    UPDATE driver 
        SET DriverLocationId = (SELECT TOP(1) CountryId FROM Country WHERE CountryCode IN ('USA','CAN', 'MEX'))
    ALTER TABLE driver ALTER COLUMN DriverLocationId INT NOT NULL   
 END

Probably when the update script from 1st attempt is executed correctly after altering.

Do you have any explanation for this?

EDIT: I have updated my question. Sorry for the double quotes. That wasn't a problem. Here is something more complicated.

EDIT: The error is:

Invalid column name 'DriverLocationId'.

EDIT (for @Stephan): GO isn't working there GO isn't working

shA.t
  • 16,580
  • 5
  • 54
  • 111
Razvan Dumitru
  • 11,815
  • 5
  • 34
  • 54
  • 2
    If you provided the actual error message for the second one, the problem would be more obvious. SQL Server is failing to *compile* all of the code, so none of the code ever executes. That's why you need the `UPDATE` that talks about the new column to be in a separate batch (so that when it compiles, the column exists) – Damien_The_Unbeliever May 08 '15 at 14:48
  • I understand. Please post this as an answer or mark as duplicate. Thanks a lot Damien. – Razvan Dumitru May 08 '15 at 14:56
  • The problem are the command go, it is ending your transaction. try with out it – MelgoV May 08 '15 at 15:26
  • 1
    @MelgoV absolutely not. GO is a batch separator in SQL server, that's all. it doesn't end or terminate a transaction. – Kris Gruttemeyer May 08 '15 at 15:32
  • Please just try this: Begin print 'ok' Go End – MelgoV May 08 '15 at 15:38
  • @MelgoV it didn't works bro'. GO isn't suitable to exist like this between begin and end. More here: http://stackoverflow.com/questions/6376866/tsql-how-to-use-go-inside-of-a-begin-end-block – Razvan Dumitru May 08 '15 at 15:40
  • 3
    @MelgoV - `BEGIN ... END` are not transaction related. Transactions are controlled by `BEGIN TRANSACTION` and either `ROLLBACK [TRANSACTION]` or `COMMIT [TRANSACTION]` - so whilst you may have a valid point about the control structure being broken up, it's got nothing to do with transactions. – Damien_The_Unbeliever May 08 '15 at 16:15

1 Answers1

3

First: Remove the extra single quotes for ''USA'' and use 'USA' instead. The same for the other values of course.

And then, as i said into my comment: In the second case SQL does not yet see that the column has been added. The mentioned GO statement would make that work, but does not work in this code because it is between the BEGIN and END tags. I believe that EXEC executes the statement in a new session and therefore is not hindered by this problem.

Razvan Dumitru
  • 11,815
  • 5
  • 34
  • 54
Tom
  • 747
  • 5
  • 16
  • The string opens at 'UPDATE... What your suggesting will close the string at ...WHERE ContryCode IN (' – Chains May 08 '15 at 14:46
  • 1
    I have updated my question. Sorry for the double quotes. That wasn't a problem. Here is something more complicated. – Razvan Dumitru May 08 '15 at 14:48
  • In the second case SQL does not yet see that the column has been added. The mentioned GO statement would make that work, but does not work in this code because it is between the BEGIN and END tags. I believe that EXEC executes the statement in a new session and therefore is not hindered by this problem. – Tom May 11 '15 at 10:05