Oracle update multiple columns in multiple tables

Question

Need a bit of help on a query for Oracle. Basically I would like to put together a query that updates a number of columns to replace actual data with test data. To do this we need to do things like change project codes in two tables, but the references must match, so for example I'm looking to do...

UPDATE table1 t1, table2 t2 
SET t1.project_ref = concat('projectRef-',rownum), 
    t2.project_ref = t1.project_ref 
WHERE t1.project_ref = t2.project_ref and t1.project_client = XYZ

This would change t1.project_ref and t2.project_ref to something like 'projectRef-1' for client XYZ.

Any ideas?

Regards

KS

Hmmmm, going to be awkward if I have 1,279 projects reference numbers, I update in one table, then have to go through and do it in the second. How will I know which relates to which. Kind of hoping Oracle could do something clever here (it costs enough, I'd like it to earn it's fees :-D ) — KS1
– KS1, Commented May 13, 2013 at 12:43

bitmagier · Accepted Answer · 2013-05-13 14:35:28Z

4

Generally updates on joins are possible in Oracle.

This is the syntax: update (select T1.* from T1, T2 where .. ) set ...

They require a join condition between T1 and T2, and a unique index on the join column in T2 to assure that the select returns at most one column per row in T1. Unfortunately for you there is another limitation that says, that only columns in one of the tables can be updated (T1 here).

So you need an intermediate table where you insert the changeset of project_ref to new-project_ref and then use this table to update T1 and T2 separately.

answered May 13, 2013 at 14:35

bitmagier

7201 gold badge6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Schmitt · Accepted Answer · 2013-05-13 13:59:37Z

2

I'm afraid you're going to have to build a translation table. Something like:

project_test_data
-----------------
real_project_ref varchar2(30)
test_project_ref varchar2(30)

Then write update statements substituting the test version for the real version in the relevant tables.

Note that if you have defined foreign key relationships on these columns you will need to disable them, or at least make them deferrable and deferred. This is one reason to avoid using meaningful data as primary keys.

edited May 13, 2013 at 13:59

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

answered May 13, 2013 at 13:08

APC

147k20 gold badges180 silver badges289 bronze badges

Collectives™ on Stack Overflow

Oracle update multiple columns in multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related