Update a result set without updating table

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

General SQL Server Forums

New to SQL Server Programming

Update a result set without updating table

Author

Topic

Littleterry
Starting Member

9 Posts

Posted - 2008-08-27 : 09:52:39

I have the following table and I want to write a query using the WHILE loop, to update the blanks in the table to nearest nValue that came before it with (in the result set) without updating the table.

Table
-----
Date pValue nValue
1/1/2008 3 5
1/2/2008 5 1
1/3/2008 6 null
1/4/2008 8 null
1/5/2008 2 null
1/6/2008 3 8
1/7/2008 4 null
1/8/2008 6 null
1/9/2008 8 4
1/10/2008 9 null

Help Plz Terry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 09:58:10

Are you using SQL 2005 or 2000?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:00:11

[code]SELECT t.Date,
t.pValue,
COALESCE(t.nValue,(SELECT TOP 1 nValue FROM YourTable WHERE Date<t.Date ORDER BY Date DESC))
FROM YourTable t[/code]

Littleterry
Starting Member

9 Posts

Posted - 2008-08-27 : 10:10:08

I'm Using SQL 2005 - Is there any way to do it using a "while loop"

Littleterry
Starting Member

9 Posts

Posted - 2008-08-27 : 10:14:39

This is the results i want achieve
------
Date pValue nValue
1/1/2008 3 5
1/2/2008 5 1
1/3/2008 6 1
1/4/2008 8 1
1/5/2008 2 1
1/6/2008 3 8
1/7/2008 4 8
1/8/2008 6 8
1/9/2008 8 4
1/10/2008 9 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:19:20

quote:
Originally posted by Littleterry

I'm Using SQL 2005 - Is there any way to do it using a "while loop"

then use this

SELECT t.Date,t.pValue,COALESCE(t.nValue,t1.nValue)
FROM YourTable t
OUTER APPLY(SELECT Top 1 nValue
            FROM YourTable
            WHERE Date < t.Date
            ORDER BY Date DESC) t1

Littleterry
Starting Member

9 Posts

Posted - 2008-08-27 : 16:57:53

Didn't work i got back nulls

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 03:48:36

[code]SELECT t.Date,t.pValue,COALESCE(t.nValue,t1.nValue)
FROM YourTable t
OUTER APPLY(SELECT Top 1 nValue
FROM YourTable
WHERE Date < t.Date
AND nValue IS NOT NULL
ORDER BY Date DESC) t1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources