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

Answers


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


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.


 update 

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

 set p1 = p2;

Need Your Help

Wait unlill an ajax request is done

javascript jquery ruby-on-rails ruby ajax

There is a link on a page. I want to send an ajax request when an user click on. And only after (when an ajax request is done) that they should be redirected to a page this link refers to.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.