Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Update the table based on the 1st 3 rownum of unique Id

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.

Tags:

2

Answers


  1. 0 
    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. 0

    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