I am creating a script wherein I need to update the table and set the STATUS to ‘A’ with conditions:

• if the status is equal to ‘N’ and

• if the count of distinct ID is less than or equal to 5.

I have found a workaround that could help me with my problem. However, I am getting an error, and I am not sure how to solve it.

TableName: TB_ABCD

---------------------
ID        |   STATUS
---------------------
11111     |  N
11111     |  N
11111     |  Y
22222     |  N
22222     |  N
33333     |  Y
44444     |  N
44444     |  N
55555     |  Y
55555     |  Y
---------------------

I am using SQL Developer and the query is like this:

with cteData as (
select count(distinct (case when status='N' then ID end)) as rn from tb_abcd
)
update cteData set status='A' where rn <= 5;

I cannot compile due to the error in update line statement. The error says: “Syntax error. Partially recognized rules (railroad diagrams).

Thanks in advance for you help.