UPDATE two columns based on JOIN

Question

This should be a simple UPDATE, which in my opinion should update the same record twice on two different columns, but it doesn't work.

A simplified sample code:

DECLARE @nowWithDate DATETIME = GETDATE() DECLARE @result TABLE(Id INT, Poll1Date DATETIME, Poll2Date DATETIME) INSERT INTO @result VALUES(1, NULL, NULL) ;WITH data(Id, IsPoll1, IsPoll2) AS ( SELECT 1, 1, 0 UNION ALL SELECT 1, 0, 1 ) UPDATE r SET Poll1Date = IIF(d.IsPoll1 = 1, @nowWithDate, Poll1Date), Poll2Date = IIF(d.IsPoll2 = 1, @nowWithDate, Poll2Date) FROM @result r JOIN data d ON d.Id = r.Id SELECT * FROM @result

I can't figure it out, why the second date not updated? It looks like this row is updated only once.

why the second date is not provided? ... what does this mean? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 8, 2017 at 11:21
Think about it. You've got two rows in data that match the ID @result, and you're updating both columns in @result each time. Whichever row it processes second overwrites the values updated from the first update, no? Sounds like you need to group on the ID in your update statement and use MAX on the IsPoll columns. — Bridge
– Bridge, Commented Feb 8, 2017 at 11:23

Bridge · Accepted Answer · 2017-02-10 15:18:07Z

DECLARE @nowWithDate DATETIME = GETDATE() DECLARE @result TABLE(Id INT, Poll1Date DATETIME, Poll2Date DATETIME) INSERT INTO @result VALUES(1, NULL, NULL) ;WITH data(Id, IsPoll1, IsPoll2) AS ( SELECT 1, 1, 0 UNION ALL SELECT 1, 0, 1 ) select * from data

This returns 2 rows. So, when you join it with @results it updates from the first row only. For the first row, IsPoll1=1 but IsPoll2=0, So, second one is not updated.

To update both the rows,

DECLARE @nowWithDate DATETIME = GETDATE() DECLARE @result TABLE(Id INT, Poll1Date DATETIME, Poll2Date DATETIME) INSERT INTO @result VALUES(1, NULL, NULL) ;WITH data(Id, IsPoll1, IsPoll2) AS ( select Id, SUM (IsPoll1),SUM(IsPoll2) from( SELECT 1 as Id, 1 as IsPoll1, 0 as IsPoll2 UNION ALL SELECT 1 as Id,0 as IsPoll1, 1 as IsPoll2) d1 group by d1.Id ) UPDATE r SET Poll1Date = IIF(d.IsPoll1 = 1, @nowWithDate, Poll1Date), Poll2Date = IIF(d.IsPoll2 = 1, @nowWithDate, Poll2Date) FROM @result r JOIN data d ON d.Id = r.Id SELECT * FROM @result

Collectives™ on Stack Overflow

UPDATE two columns based on JOIN

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related