-- update_from_table.sql    Update Logic allows selectively Updating Columns in one Table from Another.
--**Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) Copyright © 2008, Bruce Knox

-- PL/SQL UPDATE updateTable FROM fromTable sample code 

--   There are many ways to update Oracle table records selectively, but this one is easy to follow
--   and does not require the use of a primary key which may be unavailable.
--   The SPOOL command is used for debugging and tracking when running the .sql script from a .cmd file.  It will
--   create a text time with a .lst (UNIX) or .LST (Windows) in the same Directory (Folder) where 
--   the .sql script is started.

SET ECHO OFF

-- Change Log
-- 07/02/08 bknox Created 

SPOOL update_from_table

SET SERVEROUTPUT ON
EXECUTE dbms_output.enable(1000000);

DECLARE
  CHGS_ITEM  updateTable.ITEM%TYPE;
  CHGS_QTY   updateTable.QTY%TYPE;
  CHGS_PRICE updateTable.PRICE%TYPE;
  CURSOR Changes IS
SELECT fromTable.ITEM,
       fromTable.QTY,
       fromTable.PRICE
  FROM updateTable, fromTable
 WHERE fromTable.ITEM = updateTable.ITEM
-- for this example, selecting only Records in fromTable that had a Qty or Price change to apply to an existing record
  AND NOT (updateTable.QTY = fromTable.QTY AND updateTable.PRICE = fromTable.PRICE)
;

BEGIN
DBMS_OUTPUT.PUT_LINE ('Updates:');
OPEN Changes;
LOOP
FETCH Changes INTO CHGS_ITEM, CHGS_QTY, CHGS_PRICE;
EXIT WHEN Changes%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (CHGS_ITEM ||' Qty:'|| CHGS_QTY ||' Price:'|| CHGS_PRICE);

--UPDATE a Record
UPDATE updateTable
       SET updateTable.QTY   = CHGS_QTY,
           updateTable.PRICE = CHGS_PRICE
 WHERE updateTable.ITEM = CHGS_ITEM;

END LOOP;
CLOSE Changes;

COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Unknown error, details below');
      DBMS_OUTPUT.PUT_LINE ('Error code: '||sqlcode);
      DBMS_OUTPUT.PUT_LINE ('Error message: '||sqlerrm);
END;
/

SPOOL OFF

SET ECHO ON

EXIT