Bulk update + SQL + self join

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 开发者_高级运维?

alt text http://img10.imageshack.us/img10/1448/updateenddate.jpg

Thanks.

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);

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.