Bulk update + SQL + self join

Question

Hello All

I would like to update a column in Table with reference to other column(s) in same table.

Ex: As in figure below - I would like to update effective endate with min date whichever is greater than effective Start Date of currrent record.

How can this be acheived in T-SQL. Can this be done with single update statement ?

Thanks.

Answer 1

I suspect what you want can be accomplished with a case statement, but I can't give you code because the following part of your question makes no sense:

update effective endate with min date whichever is greater than effective Start Date of currrent record.

Answer 2

How about just using a subselect, something like the following:

UPDATE MyTable mt
SET mt.EFFECTIVE_END_DATETIME = (SELECT MIN(mt2.EFFECTIVE_START_DATETIME)
                                 FROM MyTable mt2
                                 WHERE mt2.EFFECTIVE_START_DATETIME > mt.EFFECTIVE_START_DATETIME);