Re: updating record question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 23 Dec 1999 16:41:27 -0500
Message-ID: <ih556s449jnuo61q5hunj07ii5hvmgilh2@4ax.com>

A copy of this was sent to "Joe D" <joed_at_icoms.com> (if that email address didn't require changing) On Thu, 23 Dec 1999 15:15:11 -0500, you wrote:

>I have the following query which gives me the following output.
>

if sufficient keys (primary/unique keys) are in place then:

update (select s.key_store_seqid "store id", t.order_number "order numb", t.order_status "status"
 from store s, txorder t
 where
 s.store_id = t.store_id

 and t.order_number < 100008
 and t.order_number >= 100000
 and s.key_store_seqid = 500513)

set "status" = 4
/

else

update txorder

   set order_status = 4
  where rowid in ( select t.rowid

                     from store s, txorder t

 where
 s.store_id = t.store_id

 and t.order_number < 100008
 and t.order_number >= 100000
 and s.key_store_seqid = 500513 )

/

will work as well....

>select s.key_store_seqid "store id", t.order_number "order numb",
>t.order_status "status"
> 2> from store s, txorder t
> 3> where
> 4> s.store_id = t.store_id
> 5> and t.order_number < 100008
> 6> and t.order_number >= 100000
> 7> and s.key_store_seqid = 500513
> 8>
>store id order numb status
>---------- ---------- ----------
> 500513 100000 2
> 500513 100001 2
> 500513 100002 2
> 500513 100003 2
> 500513 100004 2
> 500513 100005 2
> 500513 100006 2
> 500513 100007 2
>8 rows selected.
>
>What I need to update is the status column. I need to change the 2 to a 4. I
>am having problems
>with the query syntax. I'm a newbie to sql queries and I'm not sure how to
>join the two tables in a update query.
>Any help would be great.
>
>Thanks and Happy Holidays.
>
>Joe
>

--
See http://osi.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 Thu Dec 23 1999 - 15:41:27 CST