T SQL Query Help - 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 2005 Forums

Transact-SQL (2005)

T SQL Query Help

Author

Topic

davidkal
Starting Member

1 Post

Posted - 2008-12-31 : 16:36:33

My question is quite similar to a recent post

I have a table with column values as below -

ID StartDate
1 Dec 30 2008 2:47PM
2 Dec 30 2008 2:49PM
3 Dec 30 2008 2:49PM
4 Dec 30 2008 2:53PM
5 Dec 30 2008 2:58PM
6 Dec 30 2008 2:49PM
7 Dec 30 2008 2:49PM
8 Dec 31 2008 3:55PM

What I'm trying to do is to have a script to update some of the incorrect dates(date that is lesser than the previous row) in rows ID 6 and 7 with the StartDatevalue of row ID 5.

I have been trying to come up with a logic for quite some time but unable. Any help is much appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 18:22:04

Visakh16 will soon show you how you can accomplish this with CROSS APPLY.

E 12°55'05.63"
N 56°04'39.26"

Thiyagu_04
Starting Member

37 Posts

Posted - 2009-01-01 : 01:58:45

What your going to update Startdate column,which row u want update (6,7) or 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:26:02

[code]UPDATE t
SET t.StartDate =t1.StartDate
FROM YourTable t
CROSS APPLY (SELECT TOP 1 StartDate
FROM YourTable
WHERE ID < t.ID
AND StartDate>t.StartDate
ORDER BY ID DESC) t1
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:31:23

quote:
Originally posted by Peso

Visakh16 will soon show you how you can accomplish this with CROSS APPLY.

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-01 : 23:43:27

try this once

UPDATE urtable
SET StartDate = (SELECT Max(b.StartDate)
FROM urtable AS b
WHERE b.ID <= urtable.ID)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:14:41

quote:
Originally posted by bklr

try this once

UPDATE urtable
SET StartDate = (SELECT Max(b.StartDate)
FROM urtable AS b
WHERE b.ID <= urtable.ID)

it will simply cause unnecessary update on those records which have correct date values again with the same values

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources