Re: Problem with update
- From: "Mark D Powell" <Mark.Powell@xxxxxxx>
- Date: 8 Jul 2006 07:59:41 -0700
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 --
.
- References:
- Problem with update
- From: Identity
- Problem with update
- Prev by Date: Problem with update
- Next by Date: Re: Metalink doc Note 160451.1
- Previous by thread: Problem with update
- Next by thread: Re: Problem with update
- Index(es):
Relevant Pages
|
