Re: Update and from

In article <2hj4nu$bi3_at_snlsu1.london.sinet.slb.com>, sanjay_at_adjs09.abu-dhabi.geoquest.slb.com (Sanjay Sundaram) wrote:

>
> In article 4zJ_at_netcom.com, jdennis_at_netcom.com (John Dennis) writes:
> >
> > I'm a Sybase person out of his element. I'm trying to update a
> > table where the same key exists in another, identical table. Not
> > to push Sybase in this town but it does allow the "FROM" clause in
> > an UPDATE. For example, I have two tables, TABLE1 and TABLE2 which
> > have the columns KEY, A and B. Now I want to put TABLE1's A and B
> > values into TABLE2 WHERE TABLE1.KEY = TABLE2.KEY. It doesn't seem
> > obvious how I can do this (in Oracle). In Sybase I'd use:
> >
> > UPDATE TABLE2
> >
> > FROM TABLE1 T1,
> > TABLE2 T2
> > SET T2.A = T1.A,
> > T2.B = T1.A
> >
> > WHERE T1.KEY = T2.KEY
> >
> > So, Oracle SQL experts, what is the clear (foggy to me) way to do
> > this?
> >
>
> John,
> Here is what you would do.
>
> UPDATE TABLE2
> SET TABLE2.A = TABLE1.A, TABLE2.B = TABLE1.B
> WHERE TABLE1.KEY = TABLE2.KEY;
>
>
> That will do it.
>
> Sanjay Sundaram
> Schlumberger-Geoquest, Abu Dhabi.

That answer is nonsense. Please, people, try your solutions before posting.

What you want here is a called a "correlated update", documented in the SQL Language Reference Manual in the section on the UPDATE statement.

It would go something like this:

  update table2 t2 set (a,b) =
    (select a,b from table1 where key = t2.key)   where exists
    (select null from table1 where key = t2.key);

The second WHERE clause tells *which* rows to update. If it isn't there, *every* row in TABLE2 will be updated. In that case, if there are any keys in TABLE2 that are not in TABLE1, those rows in TABLE2 will have A and B set to NULL.

Although Sybase allows a shorthand syntax, ORACLE requires a SELECT statement if you are going to be pulling data from any other tables or rows in an UPDATE statement.

Another way to do the same thing is:

  update table2 t2 set (a,b) =
    (select a,b from table1 where key = t2.key)   where key in(select key from table1);

You may find that the performance of the two versions is not the same. Received on Mon Jan 24 1994 - 17:52:58 CET