How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014? For example, I have a table of:
id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web
2  | 23   | xyz  | 0    | mobile
3  | 24   | xyzc | 0    | web
4  | 25   | xyzc | 0    | web

I want the result to be:
id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web&amp;mobile
2  | 24   | xyzc | 0    | web
3  | 25   | xyzc | 0    | web

How do I do this in SQL Server using UPDATE and DELETE statements?
Note: Unfortunately I can't use the solutions given because SQL server 2014 does not have 'STRING_AGG' function.
Any help is greatly appreciated!
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 
;