keep the value of a previous row

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)

keep the value of a previous row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-27 : 11:11:12

Fred writes "How can I formulate in Transact SQL the following request :

UPDATE table_name1
SET
Datetemp =
CASE
WHEN column3 = '1' then column5 /* = the value of the CURRENT row from the column3 */
WHEN column3 = '2' then ??? /* I need to insert here the value of the PREVIOUS row from the column 5 */

END

FROM table_name1

Regards if someone can reach this challenge."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-27 : 11:20:58

UPDATE table_name1
SET
Datetemp =
CASE
WHEN column3 = '1' then column5 /* = the value of the CURRENT row from the column3 */
WHEN column3 = '2' then (select Column5 from table_name1 t1 where fld = (select min(fld) from table_name1 t2 where t2 .fld > table_name1.fld))
END

you might find it easier to use a temp table - especially if you want the updated value of the 'previous' field.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

fjacob
Starting Member

1 Post

Posted - 2002-08-28 : 09:23:04

quote:

The following error occurs when I try the solution below :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

With :

update inflowbase
set
tempdate =
case when prtech = '1' then validdate
when prtech = '2' then (select validdate from inflowbase t1 where validdate = (SELECT min(validdate) from inflowbase t2
where t2.validdate > inflowbase.validdate))
end
from inflowbase

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-28 : 09:57:24

Then validate must not be unique.
This needs to run on a unique column otherwise the concept of 'previous row' is meanlingless.
Can probably get round the error by putting max or distinct before validdate.
I also think I got the signs wrong - it would give the next greater rather than previous.
SELECT max(validdate) from inflowbase t2 where t2.validdate < inflowbase.validdate
Also need to make sure that the first row has prtech = '1' otherwise deal with the null from the subquery.
If you are setting from the field you are using to order then you only need one level of subquery.

set
tempdate =
case when prtech = '1' then validdate
when prtech = '2' then (SELECT max(validdate) from inflowbase t2
where t2.validdate < inflowbase.validdate)
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources