Cecil D'Souza wrote:
> I created a TEMP table with the following fields.
>
> Study varchar2(20)
> patient varcgar2 (10)
> site Varchar 2(10)
> T_0 varchar2(5)
> T_05 varhchar2(5)
> T_4 varchar2(5)
> T_6 varhchar2(5)
> T_8 varchar2(5)
>
> I am trying to insert records into that table from another table that
has
> the
> first three values for the Study, Patient and Site fields but the
values of
> the remaing columns may or may not have a value based on certain
criteria of
> Q_VAL.
>
>
> Ex. Create table TEMP as
> select study,
> patient
> site
> IMTime
> ' ', ' ', ' ', ' '
> from Received_data
> where Q_VAL in (11, 14, 15);
>
> The table is created and I get 100 records with the value of IMTime
in T_0
> while the other T_'s are null as specified. The table received_data
has
> duplicate values for
> the columns Study, Patient, Site
>
> Now I want to insert the value of field IMTime into column T_05 of
table
> TEMP
> from table received_data when Q_VAL in(13,17,20)
> Then I want to insert the value of field IMTime into column T_4 of
table
> TEMP
> from table received_data when Q_VAL in(16,18,21)
>
> When I try to INSERT the value of field IMTime into T_05 of the
TEMP table
> based on another criteria as in WHERE Q_VAL in (13, 17, 20), it
creates
> ANOTHER 100 records in the TEMP table with all the columns
> Study, patient, site, ' ', IMTime, ' ', ' ', ' '
>
> I then tried to used the UPDATE clause but it gives me an error
saying
> subquery returns more than 1 row.
>
> I am not in the office now. Will this code below work?
> UPDATE TABLE TEMP b
> set b.T_05 = (select a.IMtime from received_data a
> where a.study = b.study
> and a.patient = b.patient
> and a.site = b.site
> and Q_VAL in (13,17,20)
>
>
> Thanks for your help.
>
> Cecil
It might. Hard to say. If for any study,patient,site combination there
is more than one row in received_data with q_val in (13,17,20) then you
will get the same subquery returns more than 1 row error that you got
before. Does received_data have a primary key, or any unique identifier?
Received on Wed May 11 2005 - 11:47:55 CDT
