Re: Problem with update

Identity wrote:
> I've a table "Table1" with:
>
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
>
> Now I want to UPDATE this records using a Second Table "Table2"
>
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ....
>
> If, in table1, I've AAA I want to search in table2 the value of type AAA in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB in
> the table1.period
>
>
> How Can I create this update??
>
> After the UPDATE, I would like to obtain:
>
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
>
> Thanks

If the table_1 type column was unique in table_2 then you could just do a coordinated sub-query joining table_2.type to the table_1.type but type is non-unique in table_2 and you appear to want to associate the Nth occurrence of a type value in table_1 to the Nth occurrence of a type value in table_2. How do you know that these values in fact match? Oracle does not guarantee the physical order that rows are stored in matches the chronological order in which rows were inserted. The only way to guarantee the order in which rows are returned is to order the result set.

Having said the above, you could potentially write queries on the data as inline views that return a row number (rownum) value and then join the two inline view results. It would probably be wiser to use the row_number analytic function to order the two result sets prior to joining.

Given a specific set of data you should be able to produce the posted results as an exercise but the validity of the results for any real world application is highly questionable without the addition of sequencing key into both table_1 and table_2 so that you can definitely identify the relative occurrence of a specific type column value.

HTH -- Mark D Powell -- Received on Sat Jul 08 2006 - 09:59:41 CDT