Friday, May 3

Cross update taking values from another column based on duplicates found -SQL

I have a table_A containing duplicate records in column ID,but has distinct E_ID for each duplicate ID.I have retrieved records using below mention query.

select ID,E_ID,Comments
from table_A
where ID in (SELECT ID
                FROM table_A t where ID !=' '
                GROUP BY ID 
                HAVING COUNT(distinct E_ID) > 1
               )
group by ID,E_ID,Comments order by ID
This query will give me output like this:
Now i want to UPDATE Comments column which will give me output like below shown table. Note:please look into comments sentences carefully,you see E_ID differences.
You could see that E_IDs are cross update in Comments column for each duplicate ID.
You can use an outer apply query to generate a list of duplicates. This will work even if there is more than one duplicate.To know more about Outer Apply and Cross Apply click here.
The query can be written like this

update  org
set     Comments = 'Duplicate of E_ID ' + stuff(dup.lst, 1, 2, '')
from    Table_A org
outer apply
        (
        select  ', ' + cast(dup.E_ID as varchar) as [text()]
        from    Table_A dup
        where   dup.ID = org.ID
                and dup.E_ID <> org.E_ID
        for xml path('')
        ) dup(lst) where ID!=' 'and ID is not null;

The first query shows with in the Table_A, Now if you have another table called Table_B  which has same Columns and if you want to check ID in Table_A and Table_B and want to update Table_A based on duplicate IDs found between Table_A and Table_B below query can used.
update  org
set     Comments = 'Duplicate of E_ID ' + stuff(dup.lst, 1, 2, '')
from    Table_A org
outer apply
        (
        select  ', ' + cast(dup.E_ID as varchar) as [text()]
        from    Table_B dup
        where   dup.ID = org.ID
                and dup.E_ID <> org.E_ID
        for xml path('')
        ) dup(lst) where ID!=' 'and ID is not null;
I have posted this question in stackoverflow . This can be helpful so i thought to put up on my blog.