skip to Main Content

I need your help. I want to create a script to update the flag to ‘X’ where

  1. flag is ‘No’ and
  2. 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


  1. UPDATE TBL_INFO 
       SET FLAG='X'
     WHERE FLAG='No'
       AND ID IN (
                  SELECT DISTINCT ID
                    FROM TBL_INFO
                   WHERE FLAG='No'
                ORDER BY ID
                   LIMIT 3
                 )
    
    Login or Signup to reply.
  2. 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:

    WITH T AS (
      SELECT DISTINCT id,
        ROW_NUMBER() over (PARTITION BY NULL ORDER BY id) as row_num
        FROM TBL_INFO
        where UPPER(flag) = 'NO'
        group by id
        ORDER BY id
    )
    SELECT * FROM T WHERE row_num <= 3
    

    Which returns your id information: ['012', '123', '574']

    Now your final query should be:

    WITH T AS (
      SELECT DISTINCT id,
        ROW_NUMBER() over (PARTITION BY NULL ORDER BY id) as row_num
        FROM TBL_INFO
        where UPPER(flag) = 'NO'
        group by id
        ORDER BY id
    )
    UPDATE TBL_INFO
        SET flag = 'X'
        WHERE UPPER(flag) = 'NO'
        AND id IN (SELECT id FROM T WHERE row_num <= 3);
    

    Which update 5 matched rows:
    enter image description here

    and your final output table is:

    select * from TBL_INFO ORDER BY id;
    

    enter image description here

    Sample Query & result: db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search