CODEWITHSUNDEEP
sqloracle-databasesql-updatecorrelated-subquery
Elevation58
Elevation58

Reputation: 13

Oracle SQL - Update Query between 2 tables

I'm trying to write (what I think is a straight forward) update query, but as I'm new to the world of SQL its a little troublesome.

My scenario:

Table1

Parent     Child     Alias
--------------------------
New        Member1   AliasABC
New        Member2   AliasDEF
New        Member3   AliasGHI

Table2

Parent     Child     Alias
--------------------------
Parent08   Member8   Alias08
Parent09   Member2   Alias09
Parent10   Member9   Alias10

The result of the query should look like:

Table1

Parent     Child     Alias
--------------------------
New        Member1   AliasABC
Parent09   Member2   AliasDEF
New        Member3   AliasGHI

I only want to update the Parent column if the Child already exists in Table2 and leave everything else untouched. I've tried using update Correlated queries, but have drawn a blank.

Update:

Partial success with this query:

update TABLE1 p1
set (p1.PARENT) = (
       select p2.PARENT
       from TABLE2 p2
       where p2.CHILD = p1.CHILD
   )

And results in:

Table1

Parent     Child     Alias
--------------------------
(null)     Member1   AliasABC
Parent09   Member2   AliasDEF
(null)     Member3   AliasGHI

Thanks in advance,
Mark

Upvotes: 1

Views: 979

Answers (3)

Goon10
Goon10

Reputation: 150

 update 

    (select a.parent p1,a.child,b.parent p2
    from table1 a, table2 b
    where a.child= b.child )

 set p1 = p2;

Upvotes: 1

georstef
georstef

Reputation: 1388

I think this will do it for oracle:

UPDATE table1 
SET 
  table1.Parent = 
    (
     SELECT table2.Parent
     FROM table2 
     WHERE table1.Child = table2.Child
    )
WHERE 
  EXISTS (SELECT table2.Parent
          FROM table2 
          WHERE table1.Child = table2.Child);

SQLFiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270583

If you want to do this in Oracle, you'll need a correlated subquery:

update table1
    set parent = (select parent from table2 where table2.child = table1.child)
    where exists (select 1 from table2 where table2.child = table1.child);

This is standard SQL and should work in all databases, particularly Oracle.

Upvotes: 3

Related Questions