The below is the data result I want to have (See the new column created).
| ID | Value | Type | New Column |
|---|---|---|---|
| 123 | 1 | A | A_B |
| 234 | 1 | B | Null |
| 345 | 2 | B | Null |
| 456 | 2 | A | A_B |
| 567 | 3 | C | Null |
| 678 | 4 | C | Null |
| 789 | 3 | A | A_C |
| 890 | 4 | A | A_C |
Here I want to add a new column with Null, and update the value under this condition:
- If there is any value match between A and B, insert A_B in the new column where the type is A
- If there is any value match between A and C, insert A_C in the new column where the type is A
Currently, I completed to create the new column. My question is how to update the records with the above condition?
I will be happy to have any tips or advice. Thanks!
CodePudding user response：
We can produce the correct values by joining the table to itself, and SQL Server allows us to use these JOINs as part of an UPDATE statement.
UPDATE t1
SET t1.[New_Column] = 'A_' t2.Type
FROM [MyTable] t1
INNER JOIN [MyTable] t2 ON t2.Value = t1.Value AND t2.Type <> t1.Type
WHERE t1.Type= 'A'
See it work here:
