INSERT A LARGE RECORD TO AN EMPTY COLUMN

[tjones9034]

I have a need to populate a table with a large records and lot of colums. I have the following tables involved:
z_location, z_activity and z_invoice.

I added a new column (workorder_no)from z_invoice to z_location.....now having the following colums: Note: Workorder_no is an empty column.

Loccode
Location
Loc_Markup
workorder_no


I need to insert about 137,000 records to this column (workorder_no) in z_location table from the workorder_no values in z_invoice table.

My problem is that I have to retrieve the values of a column (workorder_no in z_invoice table based on this condition: RETRIEVE THE VALUES OF WORKORDER_NO FROM TABLES Z_ACTIVITY AND Z_INVOICE WHERE Z_ACTIVITY.ITEM_NO = Z_INVOICE.ITEM.NO

THEN POPUPULATE THE VALUES OF WORKORDER_NO RETRIEVED FROM THESE TWO TABLES TO THE NEWLY CREATED WORKORDER_NO IN Z_LOCATION TABLE ABOVE.

ANY HELP PLEASE? THANKS.

 

[smart99]

Use a cursor to fetch the the records i.e workorder_no in z_invoice table.

Using update/ insert whichever is appropriate use the DML statement to update the workorder_no in the location table.

Execute the cursors till cursor records exists

 

[smart99]

if u need all the data into a single column then open a cursor,loop it and append all the values with a delimeter to variable and then insert or update the variable value to the column.

 

[kccbb]

Do you mean that you need to :

update Z_LOCATION
set workorder_no =
(
select Z_ACTIVITY.workorder_no
from Z_ACTIVITY, Z_INVOICE
WHERE Z_ACTIVITY.ITEM_NO = Z_INVOICE.ITEM.NO
)
where ......

Is that what you want?

 

[tjones9034]

Thanks guys, I was able to come up with this program below and it worked perfectly.




DECLARE
CURSOR cur_wo
IS
SELECT a.workorder_no AS workorder_no, a.loccode
FROM z_activity a, z_invoice i
WHERE a.item_no = i.item_no;

wo_rec cur_wo%ROWTYPE;
BEGIN
OPEN cur_wo;

LOOP
FETCH cur_wo INTO wo_rec;

UPDATE z_location2
SET workorder_no = wo_rec.workorder_no
WHERE loccode = wo_rec.loccode;

COMMIT;
EXIT WHEN cur_wo%NOTFOUND;
END LOOP;

CLOSE cur_wo;
END;