How To Update a Table Row with a Record

Q

How To Update a Table Row with a Record? - Oracle DBA FAQ - Working with Database Objects in PL/SQL

✍: FYIcenter.com

A

If you have a RECORD variable with data fields matching a table structure, you can update a row in this table with this RECORD variable using the UPDATE ... SET ROW statement as shown in the sample script below:

CREATE TABLE emp_temp AS SELECT * FROM employees;

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  manager employees%ROWTYPE;
BEGIN
  SELECT * INTO manager FROM employees
    WHERE employee_id = 100;
  manager.employee_id := 299;
  INSERT INTO emp_temp VALUES manager;
  manager.first_name := 'FYI';
  manager.last_name := 'Center';
  UPDATE emp_temp SET ROW = manager WHERE employee_id = 299;
  DBMS_OUTPUT.PUT_LINE('# rows updated = ' || SQL%ROWCOUNT); 
END;
/
# rows updated = 1

2007-04-26, 5010👍, 0💬