UPDATE SET N + 1 equal to a row (with iteration)

All we need is an easy explanation of the problem, so here it is.

Question: How would you iterate over the previous result (referenced below) over for each of the values in column b3?

MariaDB Fiddle (this includes prior fiddle information to solve the previous question): https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=222f694a36cc41131fe558438e1d6ccd

NOTE: this is a continuation from a problem referenced here:
Update SET N + 1 equal to a row

Requesting a solution when adding 1 more dimension of complexity.

Input Table: 3 columns (iteration for each b3)

b1  b2  b3
1   X   P1
2   Z   P1
3   X   P1
4   Y   P1
5   Z   P1
6   X   P1
7   Y   P1
8   X   P2
9   Z   P3
10  X   P3
11  Z   P3
12  X   P3
13  Z   P2
14  Z   P3

Desired Result: 3 columns (iteration for each b3)

b1  b2  b3
1   X   P1
2   Y   P1
3   X   P1
4   Y   P1
5   Z   P1
6   X   P1
7   Y   P1
8   X   P2
9   Z   P3
10  X   P3
11  Y   P3
12  X   P3
13  Y   P2
14  Y   P3

Simple iteration for reference on this data:

— This code increments count and then iterates over each value of b3
— Then sets the resulting values to b2.

UPDATE b
JOIN(
    SELECT b1,
        row_number() over (partition by b3
        order by b1) rn FROM b) n on n.b1 = b.b1
SET b2 = rn;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can easily extend my solution from the previous answer as:

update b 
   set b2 = 'Y'
where exists (
  select 1 from (
      select b1, b2, lag(b2) over (partition by b3 order by b1) as lag_b2 
      from b
  ) as t 
  where t.b2 = 'Z' 
    and t.lag_b2 = 'X'
  and t.b1 = b.b1
);

select * from b order by b1;

Only rows within the same set of b3 will be taken into consideration

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply