Update a subset of a table

• Me68

  Grasshopper

  Points: 13

  More actions

  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.

• Vedran Kesegic

  SSCrazy Eights

  Points: 9302

  More actions

  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
  

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 2, 2012 at 2:17 am

  #1454068

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

  John