0

I'm trying to conditionally add a column and then update it in a single block:

IF NOT EXISTS (
    SELECT *
    FROM sys.columns
    WHERE [name] = N'outOf'
        AND [object_id] = object_id(N'woRoute')
) BEGIN
    ALTER TABLE woRoute
    ADD outOf int NULL;

    UPDATE woRoute set outOf = sampling
    WHERE sampling is not null and sampling <> 0;

    UPDATE woRoute set sampling = 1
    WHERE outOf is not null;
END

but I get an error that "outOf" isn't a valid column, referencing the first UPDATE query.

I'm running this from Microsoft SQL Server Management Studio. If I run each piece separately, it all works fine. But if I try to run them together, I get that error.

How can I add this column, copy the value from another column in the table to it, and then update the other column's value, all under the condition that the new column doesn't exist yet?

Mar
  • 7,765
  • 9
  • 48
  • 82
  • "How can I [do it]?" - You probably shouldn't. But if you insist, you could use [sp_executesql](https://msdn.microsoft.com/en-us/library/ms188001.aspx) for instance. – JimmyB Nov 04 '16 at 16:48
  • 1
    @JimmyB why should I not do so? – Mar Nov 04 '16 at 16:49
  • @MartinSmith Nope, it's correct. I only want to set it to 1 for the rows where I copied a value over from `sampling`. That way it's e.g. `sampling 1 outOf 5` instead of `sampling 5 outOf 5` – Mar Nov 04 '16 at 16:49
  • @MartinCarney Well, I guess, as a one-time administrative action it should be ok actually. For anything else, beware the implications on transactions; see [here](http://stackoverflow.com/questions/1043598/is-it-possible-to-run-multiple-ddl-statements-inside-a-transaction-within-sql-s) for instance. – JimmyB Nov 04 '16 at 16:54
  • 1
    The reason this doesn't work is because the batch of statements can't compile -- you can't reference nonexistent columns (unless, and as a special exception, the entire table didn't exist when the batch started, but I'm not getting into that one). If you can't split this up in batches (with a `GO`), then dynamic SQL (wrapping `UPDATE`s as a string for `EXEC`) is the only way to go. If you can split it up in batches, you could use a temp table to record the result of the check, but that's rather clunky. – Jeroen Mostert Nov 04 '16 at 16:56

0 Answers0