UPDATE Check

Question

UPDATE Check

mwendeck

SSC Rookie

Points: 45
More actions
September 26, 2011 at 6:37 pm

#257974

I am I using the Update statement correctly here? I have a table called Part (PK =PartId)which is tied to a supplier in a table named Part_Supplier (PK =SupplierId, FK =PartId) which is tied to a table called Inventory (PK =InventoryId FK =PartId). I want to set Part_Cost that is in the Part table to a Vendor cost in the Inventory table. Would this update statement be correct?
UPDATE PART
SET Part_Cost = I.Vendor_Cost
FROM Part_Supplier PS
INNER JOIN Inventory I on I.Part_Id = PS.Part_Id
Thanks for your help...or assurance 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

mwendecker7686 (9/26/2011)
I am I using the Update statement correctly here? I have a table called Part (PK =PartId)which is tied to a supplier in a table named Part_Supplier (PK =SupplierId, FK =PartId) which is tied to a table called Inventory (PK =InventoryId FK =PartId). I want to set Part_Cost that is in the Part table to a Vendor cost in the Inventory table. Would this update statement be correct?
UPDATE PART
SET Part_Cost = I.Vendor_Cost
FROM Part_Supplier PS
INNER JOIN Inventory I on I.Part_Id = PS.Part_Id
Thanks for your help...or assurance 🙂

No. It might look right and may even work right, but it isn't right. I know... I've just confused the heck out of you. 😛 In UPDATES with a join or two, you absolutely MUST include the target table (PART, is this case) in the from clause or you could get what is known as the "Halloween Effect". This is were the optimizer makes a huge mistake and goes into a sort of "double loop" mode that strongly resembles a Cartesian Product (Cross Join in SQL terms) and a sub-second update may suddenly take minutes and a 6 second update may suddenly take hours slamming many CPU's into the wall for performance in the process.

Also, your query actually does form a full blown cross join... there is no criteria joining the PART table to the other tables. All the rows in the PART table will be updated to the same value for Part_Cost in the PART table without regard to the actual Part_ID.

As for the actual functionality of the query, I have to ask... since you may have more than one supplier for a given part and each supplier may have more than one cost for the same part never mind that each supplier may have a different cost, what will you use to identify which supplier and cost to use for your update?

To get better help on this problem, you should probably read the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vince_sql Right there with Babe Points: 722 More actions · Answer 2

You should update from 'Part' then join the other two tables.

But Jeff's answer is much better 😀

http://sqlvince.blogspot.com/[/url]

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 3

i am completely agree with jeff. and manipulating the query as per him

UPDATE p

SET Part_Cost = I.Vendor_Cost

FROM PART p inner join Part_Supplier PS on p.partid=ps.partid

INNER JOIN Inventory I on I.Part_Id = PS.Part_Id

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

As Jeff hinted, you need to be very careful when you don't have one-to-one relationships between your tables. The UPDATE ... FROM syntax can cause unreported cardinality errors. This means that where there is more than one value in your source table matching the row you want to update in your target table, you really don't know which of those values will be used. Worse still, you won't know when this happens because there is no error message returned.

You have two options to combat this. First, you can use the ANSI SQL syntax (UPDATE ... FROM is proprietary to T_SQL). Your query will look something like the one below. Thanks to Joe Celko for showing me this, although I don't guarantee that it's absolutely correct since I have nothing to test it against for your situation.

UPDATE Part

SET Part_Cost

= (SELECT i.Vendor_Cost

FROM Inventory i

JOIN Part_Supplier s

ON p.partid = s.partid

WHERE Part.Part_Id = Part_Supplier.Part_Id)

WHERE EXISTS

(SELECT *

FROM Inventory i

JOIN Part_Supplier s

ON p.partid = s.partid

WHERE Part.Part_Id = Part_Supplier.Part_Id)

Your other option is to use the MERGE statement, which first became available in SQL Server 2008. I'll let you work out the syntax for yourself.

Even if your relationships are all one-to-one, I would still advise against using UPDATE ... FROM. This is in case a junior developer looks at your code and thinks it's OK to use the same construction in all circumstances. It also gets you into the habit of doing correctly every time.

John

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

John,

I agree that there's only one column to be updated here and so it won't make much difference for this example, but using the strictly ANSI syntax for UPDATE in SQL Server will cause the execution time to double if you update 2 columns and triple if you update 3 columns.

As for the proprietary nature of SQL Server's UPDATE/FROM, do you really port code between different RDBMSs that often? It's just my opinion but, to me, true portability is an absolute myth because no one follows the ANSI standards 100% so might as well use the performance enriched proprietary features of each RDBMS.

Also, even with the good code you wrote, there's still the high probability of more than one cost being available for the scenario given so far.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

ghanshyam.kundu (9/27/2011)
i am completely agree with jeff. and manipulating the query as per him
UPDATE p
SET Part_Cost = I.Vendor_Cost
FROM PART p inner join Part_Supplier PS on p.partid=ps.partid
INNER JOIN Inventory I on I.Part_Id = PS.Part_Id

I'm pretty sure that still won't account for the fact that there could be more than one cost depending on the vendor associated with inventory. It's not your fault... there's just not enough information given for this problem to solve it correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Jeff, I agree with you. Although I mentioned Mr Celko's name, in doing so I didn't intend to imply that I was taking his strict "ANSI good, proprietary bad" line. My view is that one should use ANSI SQL unless a proprietary syntax adds something in terms of performance or functionality. In terms of UPDATE...FROM, I think the opposite is true - that it's so dangerous that, in my opinion, it should only be used as a last resort. When I say "last resort", I mean if it can be shown that its performance is significantly better than the alternatives. In such circumstances, it should be thoroughly commented so that anyone reading the code knows that there is no possibility of cardinality errors, and no poor unsuspecting and inexperienced developers are tempted to use it elsewhere without due consideration. As always, and I should have said this in my previous post, test thoroughly before deciding which construction to use.

John

mwendeck SSC Rookie Points: 45 More actions · Answer 8

Thanks for all the information you have provided to me. Very Helpful! Thanks Again!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

CELKO (9/27/2011)
A table has to be a set, so unless you truly have one and only one part, then you want a plural or collective name.

Heh... that's one more thing we disagree on. It does keep life interesting. 😉

Haven't heard lately... how are you doing on the new edition of your "Trees" book?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)