Post reply

Update a subset of a table

  • March 1, 2012 at 10:47 pm

    #269344

    Hi,

    I have a problem when implementing an Update statement.

    I have a table where one of the columns has generated values. I need to verify that this column doesn't have duplicates and if it has, I need to change them. I tried a while loop with the following update, but I get errors.

    DECLARE @x = (select max(id) from B) + 1;

    UPDATE B

    SET @x = id = @x + 1

    FROM B, (SELECT DISTINCT id FROM B GROUP BY id HAVING count(*) > 1) A WHERE B.id=A.id

    This update is part of a while loop that ends when there are no duplicates for the id column.

    Is such update possible and if yes what am I doing wrong?

    If this one doesn't work, can anyone help me with a different solution?

    Thanks a lot.

  • March 2, 2012 at 2:10 am

    #1454064

    Add identity column, drop old column, rename identity column.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • March 2, 2012 at 2:17 am

    #1454068

    Surely a UNIQUE constraint on the column is the way to go here?

    John

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply