MySQL Update 1 row from multiple rows of data with same key

user_12429662 0
Viewed 267

Is it possible to update a single row from another table that might have multiple rows of data with the same key value?

A quick sampling of the data would be:

table TA
|  ID  |  Old_Text  |
| ---- | ---------- |
| 1040 | Text_1040A |
| 1045 | Text_1045A |
| 1045 | Text_1045B |
| 1050 | Text_1050A |


table TZ (before update)
|  ID  |  New_Text  |
| ---- | ---------- |
| 1040 |    NULL    |
| 1045 |    NULL    |
| 1050 |    NULL    |

I'm using the following update statement

UPDATE
  table TZ
SET
  TZ.New_Text =
    CASE 
        WHEN TZ.New_Text IS NULL THEN TA.Old_Text
        ELSE TZ.New_Text + ' ' + TA.Old_Text
    END
FROM 
  table TZ INNER JOIN table TA ON TZ.ID = TA.ID
WHERE TZ.ID = TA.ID

and the expected outcome should have ID 1045 have two values (Text_1045A Text_1045B) and the others have 1. Something like the below:

table TZ (after update)
|  ID  |          New_Text          |
| ---- | -------------------------- |
| 1040 |   Text_1040A               |
| 1045 |   Text_1045A Text_1045B    |
| 1050 |   Text_1050A               |

Instead I get this:

table TZ (after update)
|  ID  |    New_Text     |
| ---- | --------------- |
| 1040 |   Text_1040A    |
| 1045 |   Text_1045A    |
| 1050 |   Text_1050A    |

What am I doing wrong or not understanding with how I think update works?

row sql-update sql
2 Answers

A row gets updated only once. You need to aggregate before updating:

UPDATE TZ
    SET TZ.New_Text = CONCAT_WS(' ', TZ.New_Text, TA.Old_Text)
FROM table TZ INNER JOIN
     (SELECT TA.ID, STRING_AGG(tA.OldText, ' ') as OldText
      FROM table TA 
      GROUP BY TA.ID
     ) TA
     ON TZ.ID = TA.ID;
user_11440350

While it's a best practice to avoid the duplicates in the first place (by using constraints). Sometimes you're not in a situation to prevent them. Below I've provided a way to get the output you've described.

This SQL Concatenates the text and Updates for the RespectiveID.

This also cleans up Duplicate Records

SELECT 
    ID,
    (SELECT SUBSTRING(
    (SELECT ' ' + s.Old_Text
    FROM SampleTable s
    WHERE s.ID = sTable.ID
    --ORDER BY s.Old_Text
    FOR XML PATH('')),2,200000)) [Old_Text]
INTO #Updates
FROM SampleTable sTable
GROUP BY ID

UPDATE s
SET s.Old_Text = u.Old_Text
FROM SampleTable s
INNER JOIN #Updates u
    ON s.ID = u.ID


;WITH CTE 
AS
(
    SELECT ID, Old_Text, ROW_NUMBER() OVER(Partition BY ID ORDER BY ID) [Ranking]
    FROM SampleTable
)
DELETE FROM CTE
WHERE Ranking > 1

After cleaning up any duplicates I would highly recommend placing some constraints on that table to prevent this moving forward. You don't want to have to run this as a maintenance routine indefinitely.

user_8900620
Related