Need help with update query 2

[DaveSatIBP]

I am trying to update pricing through an intermediate table. The 3 tables in question are:
updated_prices, inv_detail, and pricing_detail

The updated_prices table consists of 3 columns: description, level, and price

The inv_detail table has several columns but I would join on description for the first table and try to join the pricing_detail table on the inv_ID field.
The pricing_detail table has inv_ID, price, and level fields.

The first table has approximately 400 rows and the pricing_detail table has over 3000 rows. I only need to update the rows in the pricing_detail table if exist in the update_prices table.

Any suggestions would be greatly appreciated.

 

[redlam]

if i follow you correctly - i think this is what you want to do:
update price_details
set [price] = upd.[price],
[level] = upd.[level]
from updated_prices upd join
inv_detail inv on
upd.[description] = inv.[description] join
price_detail pri on
inv.inv_id = pri.inv_id

 

[DaveSatIBP]

when I try to adapt this sample query into my scenario, the query parses correctly but when I execute it I receive the following error:
Invalid object name 'price_details' in the line where my update statement is located. When I run a select statement on price_details I receive output.

Any thoughts?

 

[tlbroadbent]

The query references price_details in the Update clause and price_detail in the last Join. The first post referred to pricing_detail. Which one is correct and did you use that name in the test? Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[DaveSatIBP]

I tried to simplify my request by using aliases on my initial post, but that might have just confused me further.

Here is the exact query that I am trying to run.

begin tran

update prd_pricing_detail_104
set prd_price = upd.prd_price,
plt_id = upd.[price level]

from edwards_unfaced upd join
ind_inventory_detail_104 inv on
upd.ind_officecode = inv.officecode join
prd_price pri on
inv.id = pri.ind_id

select prd_price
from prd_pricing_detail_104
rollback tran

And here is the error message from query analyzer:
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'prd_price'.

I hope this adds more clarity.

Dave

 

[tlbroadbent]

SQL simply says, "prd_price doesn't exist."

upd.ind_officecode = inv.officecode join
prd_price pri on
inv.id = pri.ind_id

Shouldn't the reference to prd_price be

upd.ind_officecode = inv.officecode join
prd_pricing_detail_104 pri on
inv.id = pri.ind_id
Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[DaveSatIBP]

Terry,
I changed the query like you suggested and now I receive the message:
Invalid column name 'officecode'

The third table prd_pricing_detail_104 does NOT have field to reference 'officecode'. I am trying to associate the officecode between the first 2 tables edwards_unfaced and ind_inventory_detail_104.

The first and second tables have a common link with the following fields:
edwards_unfaced.officecode = ind_inventory_detail_104.officecode

The second and third tables have a common link with the following fields:
ind_inventory_detail_104.ID = prd_pricing_detail_104.ind_id

The first and third tables have a common link with the following fields:
edwards_unfaced.[price level] = prd_pricing_detail.plt_id

I have made sure that all of the fields are of the same type with one another.

Thanks for your help and patience.

 

[tlbroadbent]

The change I suggested should have no impact on "officecode" since prd_pricing_detail_104.officecode is not referenced in the query.

update prd_pricing_detail_104
set prd_price = upd.prd_price,
plt_id = upd.[price level]

from edwards_unfaced upd
join ind_inventory_detail_104 inv
on upd.ind_officecode = inv.officecode
join prd_pricing_detail_104 pri
on inv.id = pri.ind_id

Since the only reference to officecode is in the highlighted piece of the query, the only conclusion I can reach his that officecode doesn't exist on the table ind_inventory_detail_104.

In your latest post you say the relationship between edwards_unfaced and ind_inventory_detail_104 is defined as

edwards_unfaced.officecode=ind_inventory_detail_104.officecode

yet the query example shows

upd.ind_officecode = inv.officecode

You need to review the query and make sure the column names referenced exist in the tables. Using names consistently makes coding much easier. Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[DaveSatIBP]

Terry,
I changed the line in question to:
upd.ind_officecode = inv.ind_officecode join
and the query now runs without error.

When I ran the query I received the message that 591 rows were affected. I had 425 rows in the edwards_unfaced table, how would it have affected 591 rows in the prd_pricing_detail_104 table?

Dave

 

[tlbroadbent]

Apparently, there is not a one-to-one relationship betwen your tables. Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[DaveSatIBP]

Terry,
After a little tweaking I realized that I needed the following line:

where upd.[price level] = pri.plt_id

and this solidified my one-to-one relationship.

Thanks for all of your help.
Dave