Re: Update Query - Help Needed Please

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 28 Aug 1999 14:24:55 GMT
Message-ID: <37cef110.14573465@newshost.us.oracle.com>

A copy of this was sent to Dipen Kotecha <dkotecha_at_ford.invalid> (if that email address didn't require changing) On Fri, 27 Aug 1999 15:59:12 +0200, you wrote:

>This seems quite complex to me as I do not often write updates.
>Hopefully someone can help.
>
>The following select query shows me the rows that I need to update. The
>problem is the correct syntax for turning it into an update query.
>
>SELECT t1.date
>FROM t1, t2, t3
>WHERE
>t1.date IS NULL
>and t1.id=t2.id
>and t2.date IS NOT NULL
>and t1.id=t3.id
>and t3.name IS NOT NULL
>
>Now this query will give me a whole set of NULL dates as the result.
>What I need to do is to update those dates (t1.date) to t2.date only for
>the dates returned by the above query. All three tables are linked by
>id, a unique key.
>
>Any help or advice will be greatly appreciated. Thanks in advance,
>

Here is one way:

tkyte_at_8.0> create table t1 ( id int primary key, dt date );
tkyte_at_8.0> create table t2 ( id int primary key, dt date );
tkyte_at_8.0> create table t3 ( id int primary key, name varchar2(25) );

tkyte_at_8.0> insert into t1 values ( 1, null );
tkyte_at_8.0> insert into t2 values ( 1, sysdate );
tkyte_at_8.0> insert into t3 values ( 1, 'hi' );
tkyte_at_8.0> 
tkyte_at_8.0> insert into t1 values ( 2, sysdate-50 );

tkyte_at_8.0> insert into t2 values ( 2, sysdate ); tkyte_at_8.0> insert into t3 values ( 2, 'hi' );

tkyte_at_8.0> update
  2 (
  3 SELECT t1.dt t1_date, t2.dt t2_date   4 FROM t1, t2, t3
  5 WHERE
  6 t1.dt IS NULL

  7  and t1.id=t2.id
  8  and t2.dt IS NOT NULL
  9  and t1.id=t3.id
 10  and t3.name IS NOT NULL

 11 )
 12 set t1_date = t2_date
 13 /
1 row updated.

tkyte_at_8.0> select * from t1;

        ID DT

---------- ---------
         1 28-AUG-99
         2 09-JUL-99

>
>Dipen

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Aug 28 1999 - 09:24:55 CDT