CODEWITHSUNDEEP
mysqlsqlsql-updatemariadb
Borislav Stefanov
Borislav Stefanov

Reputation: 761

MariaDB - update column with constant depending on another column values

Lets assume I have table named mytable and this it looks-like this:

enter image description here

I want to be able to update all the records in p6 column adding to the 39 but ONLY for the rows that has different p2 values. In other words the result table should look-like this:

enter image description here

I colored the p6 rows yellow to show where it should not move because all p2 cells which are grey are the same value. Green p6 rows should grow because p2 grows also so I add +39 to all of them. The last 13th row in example is again yellow because p2 on 12 row = p2 on 13 row.

With help of Gordon Linoff I managed to do something like that:

update mytable join
       (select mytable.*,
               (lag(p6) over(ORDER BY dt) +
                (case when lag(p2) over (order by dt) <> p2 then 39 ELSE 0 end)
               ) as new_p6
        from mytable
       ) tt
       on tt.id = mytable.id 
    set mytable.p6 = tt.new_p6
    where new_p6 <> mytable.p6;

But it is not updating as expected. Can you help me do it?

UPDATE WITH DATA:

id  dt  p2  p6
3   11.2.2021 15:45 536107.56   0
4   11.2.2021 15:50 536107.56   0
5   11.2.2021 15:55 536107.56   0
6   11.2.2021 16:00 536107.56   0
7   11.2.2021 16:05 536107.56   0
8   11.2.2021 16:10 609103.75   39
9   11.2.2021 16:15 609129.81   78
10  11.2.2021 16:20 609155.94   117
11  11.2.2021 16:25 609181.81   156
12  11.2.2021 16:30 609208.06   195
13  11.2.2021 16:35 609208.06   195

Upvotes: 1

Views: 321

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

If you want to enumerate the changes and multiply by 39, then you want somewhat different logic:

update mytable join
       (select tt.*,
               sum(case when p2 <> prev_p2 then 1 else 0 end) over (order by dt) as cnt
        from (select tt.*,
                     lag(p2) over (order by dt) as prev_p2
              from mytable tt
             ) tt
       ) tt
       on tt.id = mytable.id 
    set mytable.p6 = cnt * 39
    where cnt > 0;

Upvotes: 1

Related Questions