Re: How to update column with data from other table using SQL

A copy of this was sent to "Alan S" <ajs6695_at_jps.net> (if that email address didn't require changing) On Mon, 14 Jun 1999 06:25:52 -0700, you wrote:

>I have two tables where one of them (MASTER_PATIENT_INDEX) has a column
>named EPINUM which I want to replace with the data in each column with a new
>number.
>
>A second table has OLDEPI and NEWEPI for columns. OLDEPI and EPINUM are the
>values that need to be linked. I have tried several SQL statements (listed
>below), but to no avail.
>
>UPDATE MASTER_PATIENT_INDEX SET MASTER_PATIENT_INDEX.EPINUM =
>EPI_NUMBERING.NEWEPI
>WHERE MASTER_PATIENT_INDEX.EPI IN
>(SELECT EPI_NUMBERING.OLDEPI
>FROM EPI_NUMBERING, MASTER_PATIENT_INDEX
>WHERE EPI_NUMBERING.OLDEPI = MASTER_PATIENT_INDEX.EPINUM);
>
>UPDATE MASTER_PATIENT_INDEX SET MASTER_PATIENT_INDEX.EPINUM =
>EPI_NUMBERING.NEWEPI
>WHERE MASTER_PATIENT_INDEX.EPINUM = EPI_NUMBERING.OLDEPI;
>
>What could I try?
>

update master_patient_index

   set epinum = ( select newepi

                    from epi_numbering
                   where epi_numbering.oldepi = master_patient_index.epinum )
  where exists ( select newepi
                    from epi_numbering
                   where epi_numbering.oldepi = master_patient_index.epinum )

is one way.

>Thank you,
>
>Alan
>
>
>
>
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

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