Post reply

Help with Table Update which contains duplicate rows

  • September 10, 2007 at 11:29 am

    #174633

    HI ALL,
     
    I have to update a row in the table where there might be possible for duplication.
     
    For eg. There might be a code C123H which might be dupliated. But for each of these dupliation i need to update the other field with the new value. Say G,S,H
     
    Table after the opeation should look like
     
    Code      Region
     
    C123H   G
    C123H    H
    C123H   S
     
    At present my T_SQL inserts G in all the three row. Please can any one suggest how can my result table shown above can be achieved.
     
    Thanks in Advance,
     
    Vidhya

  • September 10, 2007 at 12:01 pm

    #732574

    Is there a Primary Key on this table? What is it? Please provide DDL.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • September 11, 2007 at 7:43 am

    #732781

    In case you do not have a primary key, or any way to identify the row, and have duplicates, you can do something horribly ugly with set rowcount. An example is:

    create table #alma (a int, b int)
GO
insert into #alma values (1,1)
insert into #alma values (1,1)
insert into #alma values (1,1)
insert into #alma values (2,2)
GO
set rowcount 1
GO
update #alma set b=5 where a=1
GO
set rowcount 0
GO
select * from #alma
GO

    This will update only a single row, even though there are three that would be updated were it without the rowcount.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • September 11, 2007 at 8:27 am

    #732801

    FYI to all, V posted more detail in PM. Below is the script that gave him the desired results...

    /*

        SQL to create the temp tables for my testing

     

    Create TABLE TableA

        (Area_code char(10)

        ,Home_Cipher char(1)

        ,Duplicate_Cipher char(1))

    GO

    Create TABLE TableB

        (Area_code char(10)

        ,Cipher char(1))

    GO

     

    INSERT dbo.TableB

        SELECT 'C133H','G' UNION

        SELECT 'C133H','H' UNION

        SELECT 'C133H','S' UNION

        SELECT 'C123H','H'

    GO

     

    INSERT dbo.TableA

        SELECT 'C133H', 'C', NULL UNION

        SELECT 'C123H', 'C', NULL

    GO

     

    */

     

    DECLARE @ciphers TABLE (Area_code char(10),Cipher char(1),rn INT)

     

    -- first lets put everything that needs to be inserted/updated into a

    -- table variable with a row number. This allows us to specify that one row

    -- will be used as the update row and not used in the insert statement.

    INSERT @ciphers

        SELECT

            area_code

            ,cipher

            ,ROW_NUMBER() OVER (PARTITION BY area_code ORDER BY area_code, cipher) as RN

        FROM

            tableB

     

    -- update the record in tableA where it's cooresponding record exists in @cipher as row #1

    UPDATE A

        SET duplicate_cipher = cipher

        FROM tableA A

            INNER JOIN @ciphers B

                ON a.area_code = b.area_code AND b.rn = 1

     

    -- now insert any other records (not row #1) using the info from tableA

    INSERT tableA

        SELECT a.Area_code, home_cipher, cipher

        FROM tableA A

            INNER JOIN @ciphers c

                ON a.area_code = c.area_code WHERE c.rn <> 1

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

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

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