Add Column to Table and Then Update It Inside Transaction

Add column to table and then update it inside transaction

GO is not a T-SQL command. Is a batch delimiter. The client tool (SSM, sqlcmd, osql etc) uses it to effectively cut the file at each GO and send to the server the individual batches. So obviously you cannot use GO inside IF, nor can you expect variables to span scope across batches.

Also, you cannot catch exceptions without checking for the XACT_STATE() to ensure the transaction is not doomed.

Using GUIDs for IDs is always at least suspicious.

Using NOT NULL constraints and providing a default 'guid' like '{00000000-0000-0000-0000-000000000000}' also cannot be correct.

Updated:

• Separate the ALTER and UPDATE into two batches.
• Use sqlcmd extensions to break the script on error. This is supported by SSMS when sqlcmd mode is on, sqlcmd, and is trivial to support it in client libraries too: dbutilsqlcmd.
• use XACT_ABORT to force error to interrupt the batch. This is frequently used in maintenance scripts (schema changes). Stored procedures and application logic scripts in general use TRY-CATCH blocks instead, but with proper care: Exception handling and nested transactions.

example script:

:on error exit

set xact_abort on;
go

begin transaction;
go

if columnproperty(object_id('Code'), 'ColorId', 'AllowsNull') is null
begin
    alter table Code add ColorId uniqueidentifier null;
end
go

update Code 
  set ColorId = '...'
  where ...
go

commit;
go

Only a successful script will reach the COMMIT. Any error will abort the script and rollback.

I used COLUMNPROPERTY to check for column existance, you could use any method you like instead (eg. lookup sys.columns).

Create column and insert into it within the same transaction?

Another alternative, if you don't want to split the code into separate batches, is to use EXEC to create a nested scope/batch:

Print 'Beginning Upgrade'
Begin Transaction
    -- --------------------------------------------------------------------
    USE [MyDatabase];

    /* Widgets now can be ordered and the order can be modified */
    ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL;

    EXEC('DECLARE @ind INT 
    SET @ind = 0 
    UPDATE [dbo].[Widgets]
    SET @ind = [IndexNumber] = @ind + 1;');

    ALTER TABLE [dbo].[Widgets] ALTER COLUMN [IndexNumber] [int] NOT NULL;
    -- --------------------------------------------------------------------
Commit tran
Print 'Upgrade completed'

The reason why the original code doesn't work because it tries to compile the entire batch before running it - the compilation fails and so it never even starts the transaction, let along alters the table.

Add column and update it within a transaction

Try this:

Begin Try

Begin Tran

Alter Table [Foo] Add SomeId INT NOT NULL Constraint TempConstraint Default  (1)
Alter Table [Foo] Drop TempConstraint

End Tran

Essentially what this is doing is adding the new column with a default value constraint of 1. All current rows will get a value of 1. Then the default value constraint is being removed, so there will be no default value.

Since you can't mix DDL and DML statements in a single transaction, like what you wanted to do originally, this is your only alternative.

PostgreSQL: is it possible to ALTER TABLE and INSERT to it within a single transaction?

This is currently not working. The INSERT statement gets stuck
because, when it's called, the table is still locked from the ALTER
TABLE statement that preceded it.

No, a transaction can't lock itself that way. The INSERT would be blocked if it was initiated by another transaction, but not by the one that already has a strong lock on the object. There is no problem in dropping the column and doing a subsequent INSERT in the same transaction.

The reason why it seems to be stuck is probably, as mentioned in the comments, that if you feed the sequence of queries from the question to an interactive interpreter, it would not execute any query at all, because there is no indication of the end of any query. If the interpreter is psql this sequence lacks either semi-colons or a \g meta-command at the end of queries.

A SQL query by itself does not need a semi-colon at its end, it's only when several queries can be submitted together that it's required.

Update specific column within the same table and add limit - PostgreSQL

Hmmm . . . I don't see why you are using a self-join. If you just want to delete the top 10 entities, you can use:

UPDATE posts p
    SET cooked = entity2char(strip_tags(p.cooked))
    WHERE p.id IN (SELECT p2.id
                   FROM posts p2
                   ORDER BY p2.created_at ASC 
                   LIMIT 10
                  );

---

Related Topics