Updating COLUMN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating COLUMN

Author

Topic

ChetShah
Starting Member

37 Posts

Posted - 2004-10-19 : 04:56:57

Hi,
This might be a simple problem but not sure how to tackle. I have the following query :


select distinct AS3.sibref,auth_stat_code,AS3.sdate,AS3.edate
from AuthStat_History3 AS3
where reg_sibref IS NULL
and AS3.sibref IN
(
select sibref
from AuthStat_History3 AS3
where reg_sibref IS NULL
group by AS3.sibref
having count(AS3.sibref) > 4)
order by AS3.sibref,AS3.sdate ASC

This gives the following results

sibref   auth_code          sdate                    edate
-------------------------------------------------------------
142289    RD              1996-12-17                 NULL
142289    AM              2001-12-01                 NULL
142289    C               2002-10-23                 NULL
142289    RV              2002-10-23                 NULL
142289    AM              2002-10-23                 NULL

i want to be able to update the edate so that the minimum edate row has the sdate of the next record. The result would be

sibref   auth_code          sdate                    edate
-------------------------------------------------------------
142289    RD              1996-12-17                 2001-12-01
142289    AM              2001-12-01                 2002-10-23
142289    C               2002-10-23                 2002-10-23
142289    RV              2002-10-23                 2002-10-23
142289    AM              2002-10-23                 NULL

I want to be able to write a query which does this without the use of cursors as performance may become an issue

Chet

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-19 : 07:29:54

try this:


update t1
set edate = (select min(sdate) from MyTable t2 where t1.sibref = t2.sibref and t2.sdate > t1.sdate)
from MyTable t1

haven't tested it... but in order to do this properly you need a column to order on.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources