how to UPDATE with values from subquery

From: Phil Lawrence <prlawrence_at_gmail.com>
Date: 22 Jan 2007 14:11:51 -0800
Message-ID: <1169503911.248734.171800@l53g2000cwa.googlegroups.com>

Hello,

I can write a SELECT statement that returns these three records: ROWID TM_NEW

------------------ --------

AAAQXxAAJAAAC0iAAY 20015133
AAAQXxAAJAAAC1oAAj 12373772
AAAQXxAAJAAAC1oAAm 12463472

I want to write an UPDATE statement that does this: UPDATE ex_acc_line_num_assgn

   SET accln_nbr_eff_tm = :tm_new
 WHERE rowid = :rowid

But instead of procedurally opening a cursor, looping through each of the three records,
and calling an insert cursor for each pair of ROWID and TM_NEW...

Is there a way to just do this from one UPDATE statement? i.e. from a SQL*Plus
session? I mean, I can write one SQL statement to get me the values I'm
interested in, there should be a way to write one UPDATE statement that

can make use of a subselect/inline query/what-have-you to accomplish the
same thing.

Here is the SELECT statement that returns the three records I showed above:

SELECT old.ROWID, new.accln_nbr_eff_tm tm_new   FROM EX_ACC_LINE_NUM_ASSGN OLD, EX_ACC_LINE_NUM_ASSGN NEW

 WHERE old.accln_sbscrp_id = new.accln_sbscrp_id
   AND old.accln_switch_status_cd = 'D'
   AND new.accln_switch_status_cd = 'D'
   AND old.ROWID <> NEW.rowid
   AND old.accln_nbr_exp_dt = new.accln_nbr_eff_dt
   AND old.accln_nbr_exp_tm > new.accln_nbr_eff_tm
   AND (
         old.accln_nbr_eff_dt < new.accln_nbr_eff_dt
         OR
         (
           old.accln_nbr_eff_dt = new.accln_nbr_eff_dt
           AND
           old.accln_nbr_eff_tm < new.accln_nbr_eff_tm
         )
       )

Regards,
Phil Lawrence Received on Mon Jan 22 2007 - 16:11:51 CST