CODEWITHSUNDEEP
sqloracle-database
user713972
user713972

Reputation: 3

Update duplicate columns table with join

I am having a problem for Oracle update SQL with join. I have a table A which has 5 columns (C1, C2, C3, C4, C5); table B which has 3 columns (D1, D2, D3). For table A, I only use 2 columns (C2, C3) to join table B (D1, D2), and update table A column C2 with table B column D3.

For table A, it will be lots of duplicates for both C2 and C3; but for condition C4 as 10, there will be no duplicates which is what I want to update.

For example, table A has records as: 

1,100,1500,10,'ORG'
1,200,2000,10,'ORG'
1,300,2500,10,'ORG'
2,1000,500,20,'PERSON'
2,1000,200,20,'PERSON'
2,2000,200,20,'PERSON'

You can see that for C4 as 10, there is no duplicate for C2 and C3. But for C4 as 20, there will be duplicates for C2 and C3.

For table B, it will be like

100,1500,80
200,2000,100
300,3000,200

There is no duplicates for table B, and will join B with A as A.C2 = B.D1 and A.C3 = B.D2 to update A.C2 to B.D3.

I only have to update C4=10 records to join with table B based on B.D1 and B.D2.

I have SQL as below, but failed as

ORA-01779: cannot modify a column which maps to a non key-preserved table

Can anyone tell me what is wrong with my SQL?

UPDATE ( 
  SELECT A.C2 OID, B.D3 TID FROM A, B 
  WHERE A.C2 = B.D1 AND A.C3 = B.D2 AND A.C4=10 AND B.D3 <> ' ' ) 
SET OID = TID

Thanks a lot!

Upvotes: 0

Views: 1296

Answers (3)

Thomas
Thomas

Reputation: 64645

First, it sounds like you are trying to update TableA. Second, the official ANSI specification for the Update statement does not provide for a join directly in the statement (you can have joins only through subqueries). One reason is that it would create ambiguities when duplicate rows were involved. So, you might try something like:

Update A
Set OID =   (
                Select B.D3
                From B
                Where B.D1 = A.C2
                    And B.D2 = A.C3
                    And B.D3 Is Not Null
                )           
Where A.C4 = 10
    And Exists  (
                Select 1
                From B
                Where B.D1 = A.C2
                    And B.D2 = A.C3
                    And B.D3 Is Not Null
                )

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

UPDATE A
SET C2 = (
    SELECT B.D3
    FROM B
    WHERE A.C2 = B.D1 AND A.C3 = B.D2)
WHERE A.C4=10
  AND EXISTS (
    SELECT *
    FROM B2
    WHERE A.C2 = B2.D1 AND A.C3 = B2.D2)

No need to alias for update statements. Also you have the condition on B.D2 <> ' ' and also A.C3 = B.D2, so it is just as easy to just filter on A.C3 <> ' ', since A is the target table. If A.C3 is never blank, then the condition is not even required.

Upvotes: 0

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

Can't you use an update statement like...

UPDATE A
  set c2 = (select D3
              from B
              where B.D2 <> ' ' and 
                    A.C2 = b.D1 and A.C3=B.D2)
           )
  where A.C4=10

Also, your question and the problem description itself looks like a puzzle thtis very hard to understand.

Can you post some data and create table statements so that it's easy to reproduce your case ?(from next time maybe....)

Upvotes: 1

Related Questions