How to query table for previous row data

you will need to know the technique to update with join:
https://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

now, you need to join the table to "itself", but the "previous row".

you could start with this:

UPDATE t 
   SET t.PrevPrice = p.CurrPrice      
FROM Table1 t
JOIN Table p
   on p.row = t.row - 1

Select allOpen in new window

however, this assumes that indeed the row value is always + 1 ....

Just change second Table name in angelIII query to Table1

;WITH table1_ AS (
    SELECT --itemnumber
        CurrPrice, PrevPrice, ROW_NUMBER() OVER(/*PARTITION BY itemnumber*/ ORDER BY Row) AS row_num
    FROM dbo.table1
)
UPDATE t1_curr
SET
    PrevPrice = t1_prev.CurrPrice
FROM table1_ AS t1_curr
INNER JOIN table1_ AS t1_prev ON
    /*t1_prev.itemnumber = t1_curr.itemnumber AND */
    t1_prev.row_num = t1_curr.row_num - 1


That should update all but the first row (row #1) ... I'm not sure what you mean by Row #0, since I don't see that row.  So you'll just need to update row #1 directly yourself.

Does this look right?

-- Self Join
update t
  set t.ClosePricePrev = p.ClosePrice
  from StockHist t
  join StockHist p
    on p.StockHistID = t.StockHistID
  where t.SymbolID = 326

StockHistID is the primary key.
SymbolID = 326 sets the criteria to a specific condition.

or should it be ?
   on p.StockHistID = t.StockHistID
  and t.SymbolID = 326

Thanks,

your way does not seem to do the p.Stockhistid = t.StockHistID - 1

again, are the ID values really sequential per Symbol ID, it doubt it.

so, you need to find the max(StockHistID) for the same SymbolID which is < current StockHistID

update t
  set t.ClosePricePrev = p.ClosePrice
  from StockHist t
  join StockHist p
    on p.SymbolID = t.SymbolID
  and p.StockHistID = ( select max(i.StockHistID) from StockHist i WHERE i.SymbolID = t.SymbolID And i.StockHistID < t.StockHistID )
  where t.SymbolID = 326

Select allOpen in new window

This was close;
Resutl was
ClosePrice ClosePrice Prev
43.47          43.47
43.05          43.05
42.77         42.77
43.10         43.10
43.69         43.69

Should have been

Curr          Prev
43.47      43.25
43.05      43.47
42.77      43.05
43.10      42.77
43.69      43.10

I need to implement a row-1 concept as in Prev value for row = Curr value from row-1

are the ID values really sequential per Symbol ID?

SymbolID simply represents a group of rows. Once I get this for a single SymbolID, I can expand it.

The StockHistID is the primary key and is unique for each row.

from mine of Scotts' version?

SELECT TOP (10) SymbolID, StockHistID,ClosePrice,ClosePricePrev
FROM StockHist WHERE SymbolID = 324

Here is the corrupt group for SymbolID = 324

SymbolID StockHistID ClosePrice  ClosePricePrev
324      25738377          10.10            38.06
324      25745953          10.00            38.06
324      25753523            9.94            38.06
324      25761039          10.01            38.06
324      25768563          10.12            38.06
324      25776112          10.14            38.06
324      25783657          10.14            38.06

The update query needs to return this:

SymbolID StockHistID ClosePrice  ClosePricePrev
324      25738377          10.10              9.98    Row 1 does not matter
324      25745953          10.00            10.10
324      25753523            9.94            10.00
324      25761039          10.01              9.94
324      25768563          10.12            10.01
324      25776112          10.14            10.12
324      25783657          10.14            10.14

and did my update not update the data as requested?
I am wondering if you misplaced any of the "aliases" i, t, p ...

Scott
How can I set SymbolID = 326 to update only the 45 rows that have 326 as the SymbolID?

;WITH table1_ AS (
    SELECT --itemnumber
        ClosePrice, ClosePricePrev, ROW_NUMBER() OVER(/*PARTITION BY itemnumber*/ ORDER BY Row) AS row_num
    FROM dbo.StockHist
)
UPDATE t1_curr
SET
    ClosePricePrev = t1_prev.ClosePrice
FROM table1_ AS t1_curr
INNER JOIN table1_ AS t1_prev ON
    /*t1_prev.itemnumber = t1_curr.itemnumber AND */
    t1_prev.row_num = t1_curr.row_num - 1

AngelIII,

Resutl was

ClosePrice ClosePrice Prev
43.47          43.47
43.05          43.05
42.77          42.77
43.10          43.10
43.69          43.69

Returned by your suggestion:

update t
  set t.ClosePricePrev = p.ClosePrice
  from StockHist t
  join StockHist p
    on p.StockHistID = t.StockHistID
  WHERE t.SymbolID = 326

AngleIII,

Close but not quite.

This was close;
Resutl was
ClosePrice ClosePrice Prev
43.47          43.47
43.05          43.05
42.77         42.77
43.10         43.10
43.69         43.69

Should have been  

Curr          Prev
43.47      43.25
43.05      43.47     43.47 is the Curr value from the preceding row.
42.77      43.05     43.05 is the Curr value from the preceding row.
43.10      42.77      42.77 is the Curr value from the preceding row
43.69      43.10

Requirement:
UPDATE table SET Curr = Prev from the preceding row

Hope this is a better explanation.

AngelIII

max(i.StockHistID) may not apply here.  StockHistID is not sequential within a SymbolID group.

// StockHistID is not sequential but is incremented within each SymbolID group.
// This works.

;WITH table1_ AS (
    SELECT
        SymbolID, ClosePrice, ClosePricePrev,
        ROW_NUMBER() OVER(PARTITION BY SymbolID ORDER BY StockHistID) AS row_num
    FROM dbo.StockHist
    WHERE
        SymbolID = 326
)
UPDATE t1_curr
SET
    ClosePricePrev = t1_prev.ClosePrice
FROM table1_ AS t1_curr
INNER JOIN table1_ AS t1_prev ON
    t1_prev.SymbolID = t1_curr.SymbolID AND
    t1_prev.row_num = t1_curr.row_num - 1

I thank everyone for the helpful suggestions and the prompt attention.

And if needed, you can remove the:

    WHERE
        SymbolID = 326

to redo the closing prices for ALL symbolids.

>max(i.StockHistID) may not apply here.  
>StockHistID is not sequential within a SymbolID group.

you need to consider it together with the WHERE clauses in that subquery.
it works, I have used it plenty of times :)