CODEWITHSUNDEEP
sqlsql-servert-sql
Len
Len

Reputation: 554

Why do two columns gets updated when I specified only one to be updated? and how to correct it?

I'm trying to update EventsTbl.Year if EventsTbl.Id is equals to ProceedsTbl.EventsId and EventsTbl.Year is not equals to ProceedsTbl.Year but both the EventsTbl.Year and the ProceedsTbl.Yeargets updated (They switched values).

Some EventsTbl row have multiple ProceedsTbl row so I tried using left join and outer apply but the results are not what I expected.

UPDATE  EventsTbl
SET     EventsTbl.Year = ProceedsTbl.Year
FROM    EventsTbl LEFT JOIN ProceedsTbl
ON      EventsTbl.Id = ProceedsTbl.EventsId
WHERE   EventsTbl.Year != ProceedsTbl.Year

UPDATE  EventsTbl
SET     EventsTbl.Year = ProceedsTbl.Year
FROM    EventsTbl OUTER APPLY (SELECT TOP 1 * FROM ProceedsTbl WHERE EventsTbl.Id = ProceedsTbl.TMSId AND EventsTbl.FiscalYear != ProceedsTbl.FiscalYear)
WHERE   EventsTbl.Year != ProceedsTbl.Year

EventsTbl

+-----+------+
| Id  | Year |
+-----+------+
|  64 |    0 |
|  76 |    0 |
| 124 | 2018 |
| 125 | 2018 |
| 305 | 2019 |
| 456 | 2019 |
| 555 | 2019 |
+-----+------+

ProceedsTbl

+----------+----------+------+
|    Id    | EventsId | Year |
+----------+----------+------+
|  8980909 |       64 | 2018 |
|  8980910 |       64 | 2018 |
|  8980911 |       64 | 2018 |
| 12380912 |       76 | 2018 |
| 12380913 |       76 | 2018 |
| 12380914 |       76 | 2018 |
| 12380915 |       76 | 2018 |
| 32880916 |      124 | 2018 |
| 32880917 |      124 | 2018 |
| 32880918 |      124 | 2018 |
| 32880919 |      124 | 2018 |
| 32880920 |      124 | 2018 |
| 56080920 |      125 | 2019 |
| 56080921 |      125 | 2019 |
| 56080922 |      125 | 2019 |
| 56080923 |      125 | 2019 |
| 61480923 |      305 | 2018 |
| 61480924 |      305 | 2018 |
| 61480925 |      305 | 2018 |
| 78280925 |      555 | 2018 |
| 78280926 |      555 | 2018 |
| 78280927 |      555 | 2018 |
| 78280928 |      555 | 2018 |
+----------+----------+------+

Desired Result

+-----+------+
| Id  | Year |
+-----+------+
|  64 | 2018 |
|  76 | 2018 |
| 124 | 2018 |
| 125 | 2019 |
| 305 | 2018 |
| 555 | 2018 |
+-----+------+

Upvotes: 1

Views: 76

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can first join with distinct value from ProceedsTbl. Something like below will work for you.

UPDATE  EventsTbl
SET     EventsTbl.Year = PT.Year
FROM    EventsTbl JOIN (SELECT EventsId, MAX(Year) Year
                        FROM ProceedsTbl
                        GROUP BY EventsId) PT
ON      EventsTbl.Id = PT.EventsId
WHERE   EventsTbl.Year != PT.Year

Upvotes: 1

Ritika
Ritika

Reputation: 104

Instead of joining with the whole table ProceedsTbl Try to join with distinct values sets in ProceedsTbl of Year and ID like

UPDATE  EventsTbl
SET     EventsTbl.Year = ProceedsTbl.Year
FROM    EventsTbl LEFT JOIN (Select Distinct EventsID,Year from ProceedsTbl) ProceedsTbl
ON      EventsTbl.Id = ProceedsTbl.EventsId
WHERE   EventsTbl.Year != ProceedsTbl.Year

Upvotes: 0

Related Questions