Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: can you make this faster, please
UPDATE NEW_TABLE A
SET A.address = (
SELECT B.address
FROM CUSTOMERS B, ADDRESSES C, WHERE B.cust_id = C.cust_id AND B.customer_number = A.customer_number);
All done, no PL/SQL, no looping, very fast (assuming you have the right indexes in place)
Paschal Mushubi <bs260_at_freenet.carleton.ca> wrote in message
news:01beb2e0$adc2dd40$dd897586_at_wotrmushubi...
> DECLARE
> CURSOR c_customers is
> SELECT rowid customer_rowid,
> customer_number,
> address
> FROM NEW_TABLE
> v_customers c_customers%ROWTYPE;
> BEGIN
> OPEN c_customers;
> FETCH c_customers INTO v_customers;
> WHILE c_customers%FOUND LOOP
> BEGIN
> SELECT T1.address
> INTO v_customers.address
> FROM CUSTOMERS T1,
> ADDRESES T2
> WHERE T1.customer_number =v_customers.customer_number
> and T1.cust_id = T2.cust_id;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> v_customers.address := null;
>> END get_customer_address;
> END;
> BEGIN
> UPDATE NEW_TABLE
> SET address = v_customers.address
> WHERE rowid = v_customers.customer_rowid;
> END;
> v_customers.address := NULL;
> FETCH c_customers INTO v_customers;
> END LOOP;
> CLOSE c_customers;
> COMMIT;