CODEWITHSUNDEEP
sqloracle-databasesql-update
Roy90
Roy90

Reputation: 81

SQL-Oracle Update table from itself using LAG()

Lets assume we have a table like this one:

| Year | Month | Day | ID_Office | Operations | Operations yesterday |
|------|-------|-----|-----------|------------|----------------------|
| 2016 | 12    | 31  | 9555      | 500        | 0                    |
| 2017 | 1     | 1   | 9555      | 600        | 0                    |
| 2017 | 1     | 2   | 9555      | 750        | 0                    |
| 2017 | 1     | 3   | 9556      | 800        | 0                    |

I would like to update the value from "Operations yesterday" using "Operations" from the previous row. So the result table must be like this one:

| Year | Month | Day | ID_Office | Operations | Operations yesterday |
|------|-------|-----|-----------|------------|----------------------|
| 2016 | 12    | 31  | 9555      | 500        | 0                    |
| 2017 | 1     | 1   | 9555      | 600        | 500                  |
| 2017 | 1     | 2   | 9555      | 750        | 600                  |
| 2017 | 1     | 3   | 9556      | 800        | 0                    |

I'm using the following code with function LAG(), but it is not updating the correct value.

Update table1 F1
Set f1.operations_yesterday = 
                       (Select LAG(f1.operations, 1, 0) OVER(ORDER BY year, month, day)
                        From table1 F2
                        Where 
                        F1.year= F2.year And
                        F1.month= F2.month And
                        F1.day= F2.day);

Could you give me some advices from my code about what is exactly wrong? I had success with lag() function on"Insert Into" statements, but in this case I must provide an Update statement solution.

NOTE: Note that there could be more than three fields to be ordered by. I put as anexample only Year, Month and Day, but there are more fields not related to dates. Also, it is important to detail that not everyday has values.

Thank you in advance!

Upvotes: 2

Views: 2998

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

If there are missing days and you want values from day before yesterday in such case then you could use merge:

merge into table1 t1
using (select lag(operations, 1, 0) 
              over (partition by id_office order by year, month, day) lop
         from table1 t) t2
on (t1.rowid = t2.rowid)
when matched then update set operations_yesterday = t2.lop;

Test data:

create table table1(Year number(4), Month number(2), Day number(2), 
    ID_Office number(5), Operations number(4), Operations_yesterday number(4));

insert into table1 values (2016, 12, 31, 9555, 500, null);
insert into table1 values (2017,  1,  1, 9555, 600, null);
insert into table1 values (2017,  1,  2, 9555, 750, null);
insert into table1 values (2017,  1,  3, 9556, 800, null);
insert into table1 values (2017,  1,  5, 9556, 400, null);

... and after merge:

 select * from table1;

 YEAR MONTH DAY ID_OFFICE OPERATIONS OPERATIONS_YESTERDAY
----- ----- --- --------- ---------- --------------------
 2016    12  31      9555        500                    0
 2017     1   1      9555        600                  500
 2017     1   2      9555        750                  600
 2017     1   3      9556        800                    0
 2017     1   5      9556        400                  800

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

It's not suggested to store data redundantly. However ...

You want to store data of the day before in your records. You suppose that there is a record for every day, so that day before would also be the record before ordered by date. But this knowledge doesn't really help, because it's way easier to select the day before than the record before.

It is very strange that you store day, month, and year separately instead of a mere date. I don't know what you are trying to achieve with this. We'll have to convert this clumsily.

update table1 today
set operations_yesterday =
(
  select operations
  from table1 yesterday
  where to_date(yesterday.year * 10000 + yesterday.month * 100 + yesterday.day, 'yyyymmdd')
      = to_date(today.year * 10000 + today.month * 100 + today.day, 'yyyymmdd') - 1
);

If you stored dates instead that would be simply:

update table1 today
set operations_yesterday =
      (select operations from table1 yesterday where yesterday.date = today.date - 1);

Add COALESCE (or Oracle's NVL), if you want 0 instead of null where there is no yesterday.

Upvotes: 2

San
San

Reputation: 4538

As soon as you pass the values of year, month and day inside the sub query, there is only one row available for the lag function which is always going to give you a null value. Correct way should be:

UPDATE table1 f1
   SET f1.operations_yesterday =
       (WITH table1_lag AS (SELECT ff.YEAR,
                                   ff.MONTH,
                                   ff.DAY,
                                   lag(ff.operations, 1, 0) over(ORDER BY ff.YEAR, ff.MONTH, ff.DAY) AS yesterday
                              FROM table1 ff)
           SELECT f2.yesterday
             FROM table1_lag f2
            WHERE f1.year = f2.year
              AND f1.month = f2.month
              AND f1.day = f2.day);

Upvotes: 2

Related Questions