locked
Updating duplicate records in same table RRS feed

  • Question

  • I have a table named "Table1"
    Its schema with data is like this

    Id   col2   col3   col4  col5  isUnique   dbUnique
    1    red    3M     1995  UK      1          null
    2    red    3M     1995  UK      0          null
    2    red    3M     1995  UK      0          null 
    3    red    3M     1995  UK      0          null
    4    pink   A4     2002  USA     1          null
    5    pink   A4     2002  USA     0          null

    Now my question is that, that i want to update dbUnique column of this table, such that dbUnique should contain Id of Table1 where isUnique is 1 in all duplicate rows based on col2,col3,col4 & col5

    So my updated data will look like this

    Id   col2   col3   col4  col5  isUnique   dbUnique
    1    red    3M     1995  UK      1          1
    2    red    3M     1995  UK      0          1
    2    red    3M     1995  UK      0          1
    3    red    3M     1995  UK      0          1
    4    pink   A4     2002  USA     1          4
    5    pink   A4     2002  USA     0          4

    this table has over 50,000 rows.

    How can i do this in MS SQL 2008

    markand
    Thursday, June 16, 2011 1:06 PM

Answers

  • Try:

    DECLARE @T TABLE (
    Id int NOT NULL,
    col2 varchar(10) NOT NULL,
    col3 varchar(10) NOT NULL,
    col4 smallint NOT NULL,
    col5 varchar(10) NOT NULL,
    isUnique bit NOT NULL,
    dbUnique int NULL
    );
    
    INSERT INTO @T(Id, col2, col3, col4, col5, isUnique, dbUnique)
    SELECT 1, 'red', '3M', 1995, 'UK', 1, NULL UNION ALL
    SELECT 2, 'red', '3M', 1995, 'UK', 0, NULL UNION ALL 
    SELECT 3, 'red', '3M', 1995, 'UK', 0, NULL UNION ALL 
    SELECT 4, 'red', '3M', 1995, 'UK', 0, NULL UNION ALL 
    SELECT 5, 'pink', 'A4', 2002, 'USA', 1, NULL UNION ALL
    SELECT 6, 'pink', 'A4', 2002, 'USA', 0, NULL;
    
    UPDATE
    	A
    SET
    	A.dbunique = B.Id
    FROM
    	@T AS A
    	INNER JOIN
    	@T AS B
    	ON A.col2 = B.col2
    	AND A.col3 = B.col3
    	AND A.col4 = B.col4
    	AND A.col5 = B.col5
    WHERE
    	B.isUnique = 1;
    
    SELECT * FROM @T;
    GO
    

     

     


    AMB

    Some guidelines for posting questions...

    • Proposed as answer by Chirag Shah Thursday, June 16, 2011 6:49 PM
    • Marked as answer by Stephanie Lv Monday, June 27, 2011 12:41 AM
    Thursday, June 16, 2011 3:18 PM