Updating join column of a table with data from another table

>> Tbl2:
>> ColKey1 ColData2_1 ColData2_2 ColData2_3. Tbl2 does not have a primary key, however, all 4 columns combined make each row unique.
you have a unique index to enforce this ?


which table are you updating ?
table 1 or 2

btw: updating a key column is (virtually) always a bad idea! Why do you want/need to do this at all?

As johnsone suggested, the MERGE has to be rewritten in this special case (where you need to update key columns). Keyword here is "read consistency".
See also here:
https://markhoxey.wordpress.com/2015/04/15/ora-00001-unique-key-violated-with-merge/
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1172832500346732443

Can you provide some sample data for the two tables and what you expect the results to be?

Thanks everyone for your responses. Here is some data:

Tbl1 (ColKey1 is a PK in Tbl1):
ColKey1 ColData  ColData2
123          Y             2/3/2010
122          N            2/2/2010
456          Y             3/4/2011
455         N             3/3/2011

Tbl2 (none of these columns is a PK in Tbl2):
ColKey1 ColData2_1 ColData2_2 ColData2_3
122          X                  A1                 C1
455          Y                  B1                 D1

So after UPDATE, I am expecting Tbl2 to look like this:
Tbl2:
123  X A1 C1
456  Y B1 D1

The only join that can be done between Tbl1 and Tbl2 is through ColKey1.

That prompts a few more questions.  With the example data as a guide:

- It looks like the two tables will always join on ColKey1.  If a row doesn't have a matching row is it skipped?
- What happens if the join on ColKey1 results in a Cartesian?
- Is the new value of ColKey1 based on the next highest value of ColKey1 in Tbl1?
- Does the Y/N flag (in ColData) come into play either to determine if a row should be updated or in selecting the new value?

A more generic question:
What is the business rule for which ColKey1 in tbl1 gets assigned in tbl2?  From the sample data it appears it is the current tbl2.ColKey1 +1.  If that isn't true, how do you decide which tbl1.ColKey1 is used for which tbl2.ColKey1??

Thank you very much both your responses. Kent Olson, here are your answers:

1) Yes, if a row doesn't have a matching row it'll be skipped.
2) It won't. ColKey1 in Tbl1 is the PK. In Tbl2 there might be multiple ColKey1s for each ColKey1 in Tbl1 but all of them need to be updated.
3) No. The next value of ColKey1 is based on Oracle sequence.
4) No. It does come into play to determine which ColKey1 is ColKey1_Old and which one is ColKey1_New.

slightwv, there is no specific business rule for ColKey1 in Tbl2. It is a straight insertion into Tbl2 from Tbl1. It is not Tbl2.ColKey1+1.
The ColKey1_New value from Tbl1 is obtained by using Oracle sequence in Tbl1.

Please let me know if I can answer any more questions. Really appreciate everyone's help.

From the data you provided above with expected results and your latest explanation:  It is impossible to do what you want.

There are two changes in tbl1 that you need to make in tbl2 but there is no way to decide which new value to update which old value.

You have
122          X                  A1                 C1
455          Y                  B1                 D1

What is the business rule that makes this correct:
123  X A1 C1
456  Y B1 D1

as opposed to:
456  X A1 C1
123  Y B1 D1

My apologies. I put the data incorrectly. Here is Tbl1 data again:

Tbl1:
ColKey1 ColData1 ColData2
123     A1       N
234     A1       Y
456     B1       N
657     B1       Y

So the relationship between the two values of 123 and 234 in ColKey1 in Tbl1 is through ColData1 value of A1. So Tbl2 currently has a row
with ColKey1=123. I need to update that row's ColKey1 value to 234.

slightwv, does this clarify a little better? Please let me know if I can answer more. I really appreciate your help.

Here is my complete test case.  If something doesn't work, please change the tables/column/data and post expected results based on it.

/*
drop table tab1 purge;
create table tab1(ColKey1 number, ColData1 varchar2(2),  ColData2 char(1));
insert into tab1 values(123,'A1','N');
insert into tab1 values(234,'A1','Y');
insert into tab1 values(456,'B1','N');
insert into tab1 values(657,'B1','Y');
commit;

drop table tab2 purge;
create table tab2(ColKey1 number, ColData2_1 char(1), ColData2_2 varchar2(2), ColData2_3 varchar2(2));
insert into tab2 values(123,'X','A1','C1');
insert into tab2 values(456,'Y','B1','D1');
commit;
*/

select * from tab2;

update tab2 t2 set colkey1=(select new_key from (
select coldata1,
	max(case when rn=1 then colKey1 end) new_key,
	max(case when rn=2 then colKey1 end) prev_key
from
(
	select coldata1, colkey1, row_number() over(partition by coldata1 order by coldata1, colkey1 desc) rn
	from tab1
)
group by coldata1
)
where t2.colkey1=prev_key
)
/

select * from tab2;
rollback;

Select allOpen in new window

I think my last post won't work.  If there are missing values, it will update those.

Let me work on it a little more.

slightwv, I really can't thank you enough.

Will you ONLY have two values in tbl1?

If this is possible:
Tbl1:
ColKey1 ColData1 ColData2
111     A1       N
123     A1       N
234     A1       Y
456     B1       N
657     B1       Y

Then what will tbl2 be?

I know your sample data is made up but there seems to be patterns if it represents your actual data.

Are you sure you cannot use coldata2 as an indicator?
Possibly use tbl2 ColData2_2 or ColData2_3 as lookups for tbl1.coldata1?

Yes, I cannot use ColData indicator. It has a different purpose. I cannot use a combination of ColData1(BK) and ColData2(Flag) because Tbl2 doesn't have these columns. The only relationship between Tbl1 and Tbl2 is through ColKey1.

In Tbl1, there could only be two rows for each value in ColData1. Here is Tbl1  again:
Tbl1:
ColKey1 ColData1 ColData2
111     A1       N
234     A1       Y
456     B1       N
657     B1       Y

Tbl2:
Before:
ColKey1
111
456

After:
ColKey1
234
657

Please let me know if I can answer any more questions. Really appreciate it.

johnsone and slightwv, thank you very much for your solutions. Really appreciate it.

Everyone else, thank you for responding.