i want to delete the data fetching from the cursor without using for update

Question

I have set of data filtering from the select statement which I need to delete from the table 'SHOP_MATERIAL_ASSIGN_TAB'. I have used a cursor to delete. but this caused problems when executing. any idea to get it done without using 'for update' method.

DECLARE
  CURSOR DELDATA_ IS
    SELECT B.*
      FROM IC_WQ_OLD_LOCATION_TAB A, SHOP_MATERIAL_ASSIGN_TAB B
     WHERE A.LOCATION_NO = B.LOCATION_NO
       AND A.SITE = B.CONTRACT
       AND B.LOCATION_NO = 'HYLIN001'
       FOR UPDATE;

  TEMP_ DELDATA_%ROWTYPE;

BEGIN
  OPEN DELDATA_;
  LOOP
    FETCH DELDATA_
      INTO TEMP_;
    EXIT WHEN DELDATA_%NOTFOUND;
    DELETE FROM SHOP_MATERIAL_ASSIGN_TAB WHERE CURRENT OF DELDATA_;
  END LOOP;
  COMMIT;
END;

Answer 1

Don't use PL/SQL at all.

DELETE FROM shop_material_assign_tab
      WHERE     EXISTS
                   (SELECT NULL
                      FROM ic_wq_old_location_tab a
                     WHERE     a.location_no = b.location_no
                           AND a.site = b.contract)
            AND b.location_no = 'HYLIN001'