1

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;

1 Answer 1

3

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

2
  • 1
    Thank you, I was largely unaware of the windows LAG function. It really simplified approaching this problem regardless of the complexity or dimensions added.
    – Dr. No
    Jun 18, 2021 at 14:11
  • Merge introduced in SQL2003 would further simplify the problem, but AFAIK its not supported by MARIADB yet. Jun 18, 2021 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.