Hello, I need some help for a sql query to update a field from data in another field situated 3 rows before. Say something to compute percent change from before to current position; I tried some turn around but insuccessflly.

Thanks JIM

An update for each row table JIM

As written, your request is tricky to satisfy. The source of this trickiness is the "3 rows before", as that's not really how databases "think" about data. Why is three special in this regard? What relates that row to the one you're trying to update?

I have a field with a float number, say ”price” I have another field for the price change from 3 rows before, say ”percentChange” I want to update the entire column ” percentChange” So il is easy to do this with any language wich queries DB but I cannot with just SQL.

To add to what Donal said - if I told you: "Find me the row that belongs (according to your requirement) exactly 3 rows before this row.", how would you go about finding that row by itself? Would you order all rows by something and look up the row 3 rows before this one? Would you have an ID of sorts that you can subtract 3 from to pinpoint the row? how would you know a row is "3 rows before" another row if you are not seeing them in a list, or in a list that is not ordered? Remember, we know nothing about your data, and to give good advice, we should know and not be guessing.

Perhaps best is to show us some table rows (enough so chance does not come into it), and a query that produces the rows ordered in a way that rows of importance are 3-rows apart as you expect and then also show us a result-set that you want to achieve with the query. That way we can guide you how to do it, and also check our advice validity against your sample data.

I have a field with a float number, say ”price” I have another field for the price change from 3 rows before, say ”percentChange” I want to update the entire column ” percentChange”

What would go wrong if the above "3" was changed to "1" or "10"?

What else can you say, more related to the the reality your data is supposed to represent, about the 3rd more previous row from a given row?

Donal is absolutely correct about the problem with your request. His questions and mine are geared toward getting to a more data-based view of the problem.

The rows in a table are an unordered set. They might contain values which can be used to define an ordering. If your table has such values, a good start would be to reveal what they are and what they mean.

OK

‘X’ ‘price’ ‘percentchange’

1. 10. Null
2. 12. Null
3. 11. Null
4. 11. 10
5. 11. -8,88

And so on Mais be it is easier with 2 tables

Something like

create table data
(
    value       float,
    change      float
);

insert into data select value, null
                   from generate_series
                  where start = 1
                    and stop = 10
;

update data
   set change = (value - oldvalue) / oldvalue
  from (
        select rowid,
               lag(value,3) over (order by rowid) as oldvalue
         from data
       ) as updates
 where updates.rowid == data.rowid
   and oldvalue is not null
   and value is not null
;

implements exactly your problem statement with the additional conditions of not updating rows where there is either no (as in null) current value or no value 3 rows prior (again, is null).

NOTE Revised datatype to float so that computation does not need to coerce floating point calculations

Results:

sqlite> select * from data
   ...> ;
┌───────┬───────────────────┐
│ value │      change       │
├───────┼───────────────────┤
│ 1.0   │ NULL              │
│ 2.0   │ NULL              │
│ 3.0   │ NULL              │
│ 4.0   │ 3.0               │
│ 5.0   │ 1.5               │
│ 6.0   │ 1.0               │
│ 7.0   │ 0.75              │
│ 8.0   │ 0.6               │
│ 9.0   │ 0.5               │
│ 10.0  │ 0.428571428571429 │
└───────┴───────────────────┘

Thank you a lot. I will try to implement it In the while I found another solution but this implies 2 tables, the prices and the changepercent and having the same id .

Thanks JIM