update from value in different row of same table and column

[sqlsinger]

I haven't used subqueries or self-joins a lot... not sure if that's what I need though...

Here is what is needed - all in one table:
to update the cost of an inventory item in location 1 with the cost of the same item from location 2 (if it exists in location 2)...

Items and locations are in the same table.

There are some other criteria also (qty>0, etc. but they are not important for the problem I'm having)

SO:
TABLEONE
item_no
location
cost

which looks like:
ITEM_NO LOCATION COST
B01 01 25.00
B01 02 15.00
B02 01 20.00
B03 01 15.00
B03 02 12.00

At the end of the update query I want it to be:
ITEM_NO LOCATION COST
B01 01 25.00
B01 02 25.00
B02 01 20.00
B03 01 12.00
B03 02 12.00

I know this shouldn't be that difficult but I can't quite think it through..
Any help appreciated..

 

[gmmastros]

I think your sample data is wrong. For item B01, location 02 has a cost of 15, so that item in location 1 should end up with a cost of 15 (not 25), right?

For this, I would recommend a derived table approach, but you could also do a self-join or a common table expression. I prefer derived tables, but it's mostly a style thing for me.

Anyway, I would first write a query that gets all of the costs for location '02', which is pretty easy.

Select item_no, cost
From TABLEONE
Where Location = '02'

Next, we will make this a derived table. You can think of a derived table as a query that you embed in another query, but it behaves like a table to the outer query. So...

Select TABLEONE.*, [green]Location2Cost[/green].cost
From   TABLEONE
       Inner Join [!]([/!]
         [blue]Select item_no, cost
         From   TABLEONE
         Where  Location = '02'[/blue]
         [!]) As Location2Cost[/!]
         On TABLEONE.item_no = [green]Location2Cost[/green].item_no
Where  TABLEONE.Location = '01'

Now, let me explain. The blue part is just the first query we wrote, plain and simple. The red parts make it a derived table. You must put parenthesis around the query and give it an alias (Location2Cost). The green parts demonstrate that you MUST reference the alias in the outer part of the query.

Now, to use this in a update query...

Update TABLEONE
Set    TABLEONE.Cost = Location2Cost.Cost
From   TABLEONE
       INNER JOIN (
         Select item_no, cost
         From   TABLEONE
         Where  Location = '02'
         ) As Location2Cost
         On TABLEONE.item_no = Location2Cost.item_no
Where  TABLEONE.Location = '01'

This is kinda like a self join in that the table IS joined to itself, but it also demonstrates the derived table technique.

The common table expression (CTE) version is very similar.

; With Location2Cost As
(
         Select item_no, cost
         From   @TABLEONE
         Where  Location = '02'
)
Update TABLEONE
Set    TABLEONE.Cost = Location2Cost.Cost
From   @TABLEONE As TABLEONE
       INNER JOIN Location2Cost
           On TABLEONE.item_no = Location2Cost.item_no
Where  TABLEONE.Location = '01'

The pure 'self-join' method would look like this:

Update A
Set    A.Cost = B.Cost
From   TABLEONE As A
       INNER JOIN TABLEONE As B
         On A.item_no = B.item_no
Where  A.Location = '01'
       And B.Location = '02'

I created some sample data and tested all 3 solutions, and they appear to work properly, and they all appear to have the same execution plan, so there really isn't a clear advantage to any of them. It's important that you understand the techniques used to solve this problem, because they can be applied to a wide variety of problems, not just this one.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sqlsinger]

thank you so much for explaining... I believe this is exactly what I was trying to do... I will run it and see...
Thx again...