How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014?
please, try this:
with stable as (
SELECT
    ROW_NUMBER() over(order by [code]) as [ID]
    , [code]
    , [name]
    , [type]
    , CASE
        WHEN (SUM([web]) = 1 AND SUM([mobile]) = 1) THEN 'web&amp;mobile'
        WHEN (SUM([web]) = 1 ) THEN 'web'
        WHEN (SUM([mobile]) = 1) THEN 'mobile'
        ELSE null END AS deviceType
FROM (
    select [id], [code], [name], [type], [web], [mobile]
    from (
        SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
    ) as source
    pivot 
    (
        COUNT(deviceType) FOR deviceType IN ([web],[mobile])
    ) as tb
) source
group by [code], [name], [type])
,ttable as (
SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
)
MERGE
    INTO ttable as T
    USING stable as S
    ON t.id = S.id
    WHEN MATCHED THEN
        UPDATE SET
            T.[code] = S.[code], T.[name] = S.[name], T.[type] = S.[type], T.[deviceType] = S.[deviceType]
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE 
;