Oracle query

CountingCrows

The aim is to create a generic query is to update the Target table with the values from the Loading table by referencing the Shelf to Store Assignment Table.
E.g In the first record, update value1 from 10 to 50 etc. Can this be done in a single query?

Loading Table
PROD_ID STORE_ID VALUE1 VALUE2
1 1100 50 75
2 1200 60 80

Target Table
PROD_ID SHELF_ID VALUE1 VALUE2
1 33 10 15
2 35 20 30

Shelf to Store Assignment Table
STORE_ID SHELF_ID
1100 33
1200 35

GreeBo

CountingCrows wrote: »

The aim is to create a generic query is to update the Target table with the values from the Loading table by referencing the Shelf to Store Assignment Table.
E.g In the first record, update value1 from 10 to 50 etc. Can this be done in a single query?

Loading Table
PROD_ID STORE_ID VALUE1 VALUE2
1 1100 50 75
2 1200 60 80

Target Table
PROD_ID SHELF_ID VALUE1 VALUE2
1 33 10 15
2 35 20 30

Shelf to Store Assignment Table
STORE_ID SHELF_ID
1100 33
1200 35

Can you give a better idea of what you expect, both before and after?

Also take a look at triggers.

CountingCrows

Sorry, struggling to get my head around this one so explaining isn't easy.

The query should update the value columns based on two conditions;

loading.prod_id = target.prod_id
target.shelf_id = select shelf_id from Shelf to Store Assignment where store_id = loading table.store_id

Colonel Panic

In MS SQL I do this kind of thing by updating the table from a select with a join on whatever else I need.

What I usually do, is build the results I want first and use some thing like:

update ATable set ATable.Column = SomeDerivedTable.Column from ATable as T inner join <stuff>

That might point you in the right direction?

CountingCrows

Colonel Panic wrote: »

In MS SQL I do this kind of thing by updating the table from a select with a join on whatever else I need.

What I usually do, is build the results I want first and use some thing like:

update ATable set ATable.Column = SomeDerivedTable.Column from ATable as T inner join <stuff>

That might point you in the right direction?

Will that method cope with multiple values from SomeDerivedTable.Column? E.g in my example there could be multiple shelves assigned to one store.

Colonel Panic

It will if you can manipulate the source data the way you want. I don't know anything about your data!