CODEWITHSUNDEEP
oracle-database
AJV
AJV

Reputation: 11

dynamic row to column update

I have a table A (column_name,value) which stores the metadata information.
I have another table B which has columns as rows of A with some additional system columns.
For example if table A has two rows (empID,1) and (salary,200) then table B will look like B(empid,salary,system_id,create_date,update_date).
I need pl/sql procedure which will update B and set empid as 1 and set salary as 200. The rows in A and columns in B can change dynamically.

Upvotes: 0

Views: 750

Answers (1)

GWu
GWu

Reputation: 2787

Get the list of columns and values from table A, build a statement and execute immediate:

declare
 cols varchar2(2000);
begin
 for a in (select * from a) loop
   cols := ','||a.column_name||'='||a.value||cols;
 end loop;
 cols:=substr(cols,2);
 execute immediate 'update b set '||cols;
end;
/

See http://www.sqlfiddle.com/#!4/f6976/8 for an example.

But I wonder how you are going to build the where - clause with your current DB structure. Currently you have no way to relate rows in table A to build where clause for table B.

Upvotes: 1

Related Questions