Update the first x of the records returned

[bmyers404]

I need to update values in a table for a subset of rows returned from a select statement. The select statement may return any number of records. I'll use 15 in this example. I want to update the first 9 records returned with the value of "Green" and the remaining records with the value "Yellow". I need the data to be sorted in ascending order by the Order_ID field which is the unique key for the table. nGreen is a variable containing the maximum number of "Green" records.

I've tried something like this:

UPDATE TABLE SET COLOR = 'Green' WHERE ORDER_ID = (SELECT ORDER_ID FROM TABLE WHERE STATUS = 'Open' AND ROWNUM <= NGreen ORDER BY ORDER_ID ASC);

I would follow this with a second update statement to set the remaining records with "Yellow".

Thanks for your help!

[amitpatel66]

Firstly, your update will not work if the sub query returns more than one order id.

[r035198x]

Is using a stored procedure an option?

[subashsavji]

may this be useful


DECLARE
A NUMBER;
CURSOR CUR1 IS
SELECT * FROM EMP WHERE DEPTNO=&D AND ENAME='&N'
FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR I IN CUR1 LOOP
IF I.SAL > 3000 THEN
UPDATE EMP
SET SAL= I.SAL*1.2
WHERE CURRENT OF CUR1;
END IF;
A := I.SAL;
DBMS_OUTPUT.PUT _LINE(A);
END LOOP;
-- A := SAL;
--DBMS_OUTPUT.PUT _LINE(SAL);
END;