Update using Self Join Sql Server

Question

I have huge data and sample of the table looks like below

+-----------+------------+-----------+-----------+
| Unique_ID |    Date    | RowNumber | Flag_Date |
+-----------+------------+-----------+-----------+
|         1 | 6/3/2014   |         1 | 6/3/2014  |
|         1 | 5/22/2015  |         2 | NULL      |
|         1 | 6/3/2015   |         3 | NULL      |
|         1 | 11/20/2015 |         4 | NULL      |
|         2 | 2/25/2014  |         1 | 2/25/2014 |
|         2 | 7/31/2014  |         2 | NULL      |
|         2 | 8/26/2014  |         3 | NULL      |
+-----------+------------+-----------+-----------+

Now I need to check if the difference between Date in 2nd row and Flag_date in 1st row. If the difference is more than 180 then 2nd row Flag_date should be updated with the date in 2nd row else it needs to be updated by Flag_date in 1st Row. And same rule follows for all rows with same unique_ID

update a
set a.Flag_Date=case when DATEDIFF(dd,b.Flag_Date,a.[Date])>180 then a.[Date] else b.Flag_Date end
from Table1 a
inner join Table1 b
on a.RowNumber=b.RowNumber+1 and a.Unique_ID=b.Unique_ID

The above update query when executed once, only the second row under each Unique_ID gets updated and result looks like below

+-----------+------------+-----------+------------+
| Unique_ID |    Date    | RowNumber | Flag_Date  |
+-----------+------------+-----------+------------+
|         1 | 2014-06-03 |         1 | 2014-06-03 |
|         1 | 2015-05-22 |         2 | 2015-05-22 |
|         1 | 2015-06-03 |         3 | NULL       |
|         1 | 2015-11-20 |         4 | NULL       |
|         2 | 2014-02-25 |         1 | 2014-02-25 |
|         2 | 2014-07-31 |         2 | 2014-02-25 |
|         2 | 2014-08-26 |         3 | NULL       |
+-----------+------------+-----------+------------+

And I need to run four times to achieve my desired result

+-----------+------------+-----------+------------+
| Unique_ID |    Date    | RowNumber | Flag_Date  |
+-----------+------------+-----------+------------+
|         1 | 2014-06-03 |         1 | 2014-06-03 |
|         1 | 2015-05-22 |         2 | 2015-05-22 |
|         1 | 2015-06-03 |         3 | 2015-05-22 |
|         1 | 2015-11-20 |         4 | 2015-11-20 |
|         2 | 2014-02-25 |         1 | 2014-02-25 |
|         2 | 2014-07-31 |         2 | 2014-02-25 |
|         2 | 2014-08-26 |         3 | 2014-08-26 |
+-----------+------------+-----------+------------+

Is there a way where I can run update only once and all the rows are updated.

Thank you!

Gordon Linoff · Accepted Answer · 2015-10-16 22:10:33Z

1

If you are using SQL Server 2012+, then you can use lag():

with toupdate as (
      select t1.*,
             lag(flag_date) over (partition by unique_id order by rownumber) as prev_flag_date
      from table1 t1
     )
update toupdate
    set Flag_Date = (case when DATEDIFF(day, prev_Flag_Date, toupdate.[Date]) > 180
                          then toupdate.[Date] else prev_Flag_Date
                     end);

Both this version and your version can take advantage of an index on table1(unique_id, rownumber) or, better yet, table1(unique_id, rownumber, flag_date).

EDIT:

In earlier versions, this might have better performance:

with toupdate as (
      select t1.*, t2.flag_date as prev_flag_date
      from table1 t1 outer apply
           (select top 1 t2.flag_date
            from table1 t2
            where t2.unique_id = t1.unique_id and
                  t2.rownumber < t1.rownumber
            order by t2.rownumber desc
           ) t2
     )
update toupdate
    set Flag_Date = (case when DATEDIFF(day, prev_Flag_Date, toupdate.[Date]) > 180
                          then toupdate.[Date] else prev_Flag_Date
                     end);

The CTE can make use of the same index -- and it is important to have the index. The reason for the better performance is because your join on row_number() cannot use an index on that field.

edited Oct 16, 2015 at 22:10

answered Oct 15, 2015 at 16:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Santosh Over a year ago

Thank you Gordon. Am on 2008R2 and hence lag() don't work for me. is there a way I can get this query worked on 2008? Please let me know

Collectives™ on Stack Overflow

Update using Self Join Sql Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related