vendredi 8 mai 2015

Difference between EXEC('UPDATE') and UPDATE for new column

Today i run into a problem with my sql script. I must add a not nullable column but i don't have a known value for default so i create the column as a nullable one, 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? (i have searched already on stack & other forums, but i'm not so familiar with the sql context language and probably i failed at wording and finished finding nothing)

Aucun commentaire:

Enregistrer un commentaire