Let’s say you need to update all the orders from batch_number = '37250006-11'
Working with a test table
DROP TABLE FULFILL.TESTCURSOR CASCADE CONSTRAINTS;
CREATE TABLE FULFILL.TESTCURSOR
(
ORDER_NUMBER INTEGER,
INSERT_DATE DATE DEFAULT sysdate,
VALUE INTEGER
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
So you write a query like this.
SELECT ORDER_NUMBER
FROM fulfill.ORDERS
where batch_number = '37250006-11'
Now you need to update each order and set the value to 1.
You would run it through a loop .
BEGIN
for r1 IN (
SELECT ORDER_NUMBER
FROM fulfill.ORDERS where batch_number = '37250006-11'
)
LOOP
update testcursor
set value = 1
where ORDER_NUMBER = r1.ORDER_NUMBER; END LOOP;
END;