Update: How to update the first two rows with a Y flag

oracle

I am trying to update the first two rows of a column from null to Y so that a user should be able to select two images.

Can you please tell me how to fix the query below;

DECLARE CURSOR tp_Identifier_cur
     IS
        select distinct TP_IDENTIFIER
        from IMAGES
        ORDER BY TP_IDENTIFIER ASC;
     l_tp_Identifier  tp_Identifier_cur%ROWTYPE;
  BEGIN
     OPEN tp_Identifier_cur;
     LOOP
        FETCH tp_Identifier_cur INTO l_tp_Identifier;
        EXIT WHEN tp_Identifier_cur%NOTFOUND;
        UPDATE IMAGES SET OMG = 'Y'
        where TP_IDENTIFIER = l_tp_Identifier.TP_IDENTIFIER
        and rownum <= 2
        order by Image_identifier;
     END LOOP;
     CLOSE tp_Identifier_cur;
  END;

Best Answer

You don't need a cursor for this:

update IMAGES
  set OMG='Y'
where image_identifier in (select image_identifier 
                             from (select image_identifier 
                                     from IMAGES 
                                    order by IMAGE_IDENTIFIER) 
                            where rownum<=2)

If you want 2 rows per TP_IDENTIFIER, I'd go for a window function:

update IMAGES
  set OMG='Y'
where exists   (select 1 
                  from (select image_identifier,tp_identifier,  
                               row_number() over (partition by tp_identifier order by image_identifier) as rn
                          from IMAGES 
                       ) t
                 where t.image_identifier=IMAGES.image_identifier
                   and t.tp_identifier = IMAGES.tp_identifier
                   and rn<=2)
Related Question