I need your help. I want to create a script to update the flag to ‘X’ where
- flag is ‘No’ and
- get the first 3 rownum of unique Id. E.g.
tablename: TBL_INFO
Sample table:
-------------------------
ID | FLAG
-------------------------
123 | No
456 | Yes
789 | No
123 | No
123 | Yes
456 | Yes
789 | No
789 | Yes
574 | No
574 | No
012 | No
The sample table contains 5 distinct IDs (012, 123, 456, 574, 789) but since the problem is to update only the records where flag is ‘No’ and rownum of unique Ids is less than or equal to 3.
I am using oracle sql developer and I tried to create a script something like this:
UPDATE TBL_INFO
SET FLAG='X'
WHERE FLAG='No' and rownum <= 3 order by ID.
This script is only updating the 1st 3 records.
-------------------------
ID | FLAG
-------------------------
012 | X
123 | X
123 | X
Expected output:
-------------------------
ID | FLAG
-------------------------
012 | X
123 | X
123 | X
574 | X
574 | X
456 | Yes
789 | No
789 | Yes
I am having a hard time analyzing how to get the rownum of unique IDs if it is less than/equal to 3.
Thank you.
2
Answers
Your sample table contains 5 distinct IDs (012, 123, 456, 574, 789) and you want to filter out first three ids which contains
flag='No'
.So, use MySQL script:
Which returns your id information:
['012', '123', '574']
Now your final query should be:
Which update 5 matched rows:
and your final output table is:
Sample Query & result: db<>fiddle