SQL how to change columns value in next 2 records

select 
  id,
  flg,
  case
    when lag(flg) over(order by id) = 'Y' or lag(flg, 2) over(order by id) = 'Y' then 'N'
    else flg
  end as flg1
from mytable
order by id;

Your description is wrong. You want to iterate through your rows for which you'd use a recursive query in SQL.

Number your rows first, because there can always be gaps in a table's IDs. Then use a recursive query to loop through the rows. I think this is the straight-forward to approach this.

with numbered as (select t.*, row_number() over (order by id) as rn from mytable t)
, cte (id, flg, flg1, prev_flg1, rn) as
(
  select id, flg, flg, null, rn from numbered where rn = 1
  union all
  select
    t.id,
    t.flg,
    case
      when cte.flg1 = 'Y' or cte.prev_flg1 = 'Y' then 'N'
      else t.flg
    end,
    cte.flg1,
    t.rn
  from cte 
  join numbered t on t.rn = cte.rn + 1
)
select id, flg, flg1
from cte
order by id;

You need to iterate over the rows sequentially and calculate whether to show the row before you can calculate whether to show row following it; this means you need a recursive (or hierarchical) query.

You need to split the rows up into triplets of sequential rows such that each triplet starts with a FLG = 'Y' row and then all rows that are not in such a triplet or are in the second or third rows of the triplet will have a FLG1 value of N.

Like this:

WITH find_triplets ( id, flg, flg_count ) AS (
  SELECT id,
         flg,
         DECODE( flg, 'Y', 1, 0 )
  FROM   table_name
  WHERE  id = 1
UNION ALL
  SELECT t.id,
         t.flg,
         CASE f.flg_count
         WHEN 0
         THEN DECODE( t.flg, 'Y', 1, 0 )
         ELSE MOD( f.flg_count + 1, 3 )
         END
  FROM   find_triplets f
         INNER JOIN table_name t
         ON ( t.id = f.id + 1 )
)
SELECT id,
       flg,
       CASE
       WHEN flg = 'Y' AND flg_count = 1
       THEN 'Y'
       ELSE 'N'
       END as flg1
FROM   find_triplets
ORDER BY id

Which, for your sample data, outputs:

ID | FLG | FLG1
-: | :-- | :---
 1 | N   | N   
 2 | Y   | Y   
 3 | Y   | N   
 4 | Y   | N   
 5 | Y   | Y   
 6 | Y   | N   
 7 | Y   | N   
 8 | Y   | Y   
 9 | N   | N   
10 | Y   | N   
11 | N   | N   
12 | Y   | Y   
13 | N   | N   
14 | N   | N   
15 | N   | N   
16 | Y   | Y   
17 | N   | N   
18 | N   | N   
19 | N   | N   
20 | N   | N   
21 | N   | N   
22 | Y   | Y   
23 | Y   | N   

db<>fiddle here

In my misinterpretation of the question, this is a pretty simple gaps-and-islands problem. See the edit below for an improved answer. I would suggest using the difference of row numbers to define the islands. The definition of the flag is then just checking the row number on each group of 'Y' values:

select id, flg,
       (case when flg = 'Y' and
                  mod(row_number() over (partition by flg, seqnum - seqnum_2 order by id), 3) = 1
             then 'Y'
             else 'N'
        end) as flg1
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by flg order by id) as seqnum_2
      from t
     ) t
order by id;

Here is a db<>fiddle.

If you want to update the flag, I would recommend using merge.

Note: I would also expect this to be faster (perhaps much faster) than a recursive CTE approach.

EDIT:

Alex makes a really good point. I think this requires a recursive CTE. If you have a large amount of data, it might be possible to optimize it by splitting the data into groups where you have multiple 'N's in a row. Your question doesn't mention data size.

I would approach this as:

with tt as (
       select t.*, row_number() over (order by id) as seqnum
       from t
     ),
     cte (seqnum, id, flg, flg1, counter) as (
      select seqnum, id, flg, flg,
             (case when flg = 'Y' then 1 else 0 end)
      from tt
      where seqnum = 1
      union all
      select tt.seqnum, tt.id, tt.flg,
             (case when cte.counter in (1, 2) then 'N'
                   when tt.flg = 'Y' then 'Y'
                   else 'N'
              end),
             (case when cte.counter in (1, 2) then cte.counter + 1
                   when tt.flg = 'Y' then 1
                   else 0
              end)
      from cte join
            tt
            on tt.seqnum = cte.seqnum + 1
     )
select *
from cte;

Basically, this walks through the data and finds the first 'Y'. At that point, it sets a counter to 1. In the next two rows, the counter is incremented, regardless of the value of the flag. Then it goes back to looking for a 'Y' to repeat the process.

Amusingly, this seems like a pretty simple operation to implement using a Turing machine. Usually, it is not obvious how to implement such things.

Interestingly, if you put all the flags in a string, regular expressions solve the problem very simply:

select flgs,
       substr(regexp_replace(flgs, 'Y(..|.$|$)', 'YNN'), 1, length(flgs)) as flg1s
from (select listagg(flg, '') within group (order by id) as flgs
      from t
     ) t;