CODEWITHSUNDEEP
sqlsql-servert-sql
jp2631
jp2631

Reputation: 39

Update column in table

All,

I tried to update column C_NEW in my table with data within the table but stuck not sure where to start. I hope bottom explaination help.

C1      | C2      | C3      | C4         | C_NEW
-------- --------- --------- ------------ -------
[email protected] | [email protected] | [email protected] | [email protected]    |
[email protected] | [email protected] | [email protected] | [email protected]   |
[email protected] | [email protected] | [email protected] | [email protected]  |
[email protected] | [email protected] | [email protected] | [email protected] |
[email protected] | [email protected] | [email protected] | [email protected] |
[email protected] | [email protected] | [email protected] | [email protected] |

Email need to sent to user in C1 in combination of C1 and C2, so three separate email should sent to [email protected], [email protected] and [email protected]

Copy to user in C3 that is not in C1 or C2, so in the column C_NEW I want to have ([email protected]; [email protected]) to fill in first three rows and ([email protected]) should fill in row four and five and ([email protected]) should fill in the last row in C_NEW column.

also need to copy user in column C4 that is not in C1. So the update column C_NEW should have ([email protected]; [email protected]; [email protected]; [email protected]) to fill in first three rows and ([email protected]; [email protected]; [email protected]) should fill in row four and five and ([email protected]; [email protected]) should fill in the last row in C_New column.

With the above data I should have three reports...

Report A: [email protected]
Email to:
Copy to: [email protected]; [email protected]; [email protected]; [email protected]


Report B:
Email to: [email protected]
Copy to: [email protected]; [email protected]; [email protected]

Report C:
Email to: [email protected]
Copy to: [email protected]; [email protected]

The table final result shoul look like this

C1      | C2      | C3      | C4         | C_NEW
-------- --------- --------- ------------ -------
[email protected] | [email protected] | [email protected] | [email protected]    | [email protected]; [email protected]; [email protected]; [email protected]
[email protected] | [email protected] | [email protected] | [email protected]   | [email protected]; [email protected]; [email protected]; [email protected]
[email protected] | [email protected] | [email protected] | [email protected]  | [email protected]; [email protected]; [email protected]; [email protected]
[email protected] | [email protected] | [email protected] | [email protected] | [email protected]; [email protected]; [email protected]
[email protected] | [email protected] | [email protected] | [email protected] | [email protected]; [email protected]; [email protected]
[email protected] | [email protected] | [email protected] | [email protected] | [email protected]; [email protected]

Upvotes: 2

Views: 156

Answers (1)

take
take

Reputation: 2222

You can Update your existing Data through select statement.

In this you select all C3 and C4 with your conditions ans do an UNION. This list you have to concat through XML PATH and build your C_NEW column.

UPDATE [Table]
    SET C_NEW = SelectTable.C_NEW
FROM
( 
    SELECT [Table].c1, [Table].c2, c3, c4, SUBSTRING(t2.C_NEW , 2, LEN(t2.C_NEW)) AS C_NEW
    FROM dbo.[Table]
    LEFT JOIN (
    SELECT C1, C2, (
        (SELECT ', ' + C3 AS [text()]  FROM (
            SELECT C1, c2, C3 FROM [Table]
            WHERE C3 <> C1 AND C3 <> C2 AND C1 = t.C1 AND C2 = t.C2
            UNION 
            SELECT C1, c2, C4 FROM [Table]
            WHERE C4 <> C1 AND C1 = t.C1 AND C2 = t.C2
        ) AS t3 For XML PATH (''))
    )
    AS C_NEW
    FROM [Table] AS t
    GROUP BY C1, C2, t.C_NEW
    ) AS t2 ON t2.C1 = [Table].C1 AND t2.C2 = [Table].C2
) AS SelectTable
WHERE [Table].C1 = SelectTable.C1 
    AND [Table].C2 = SelectTable.C2 
    AND [Table].C3 = SelectTable.C3
    AND [Table].C4 = SelectTable.C4

Upvotes: 1

Related Questions