CODEWITHSUNDEEP
sqloracle-database
firedude144
firedude144

Reputation: 43

Populating table from table of value

I've got a largish Oracle table (30M rows) which contains three columns: ID, fieldname, value. I need a query that will update the target table (which contains 93 columns) from the source data. So if the first row of the source table is 1,'first_name','Robert' then this will update the row where ID=1 updating first_name column with the value 'Robert'.

Is this even possible with a query or do I need to process it with another tool?

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Hmmm. You can do this with a query. I would suggest building an index on the first table on id, fieldname, value and then running the following update 93 times:

update targettable tt
    set field1 = (select max(value) from sourcetable st where st.id = tt.id and st.fieldname = 'field1')
    where exists (select 1 from sourcetable st where st.id = tt.id and st.fieldname = 'field1');

You can actually write this all as one query, but it gets complicated to handle rows where only some fields are updated.

Upvotes: 1

Related Questions