SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATE Check


UPDATE Check

Author
Message
mwendeck
mwendeck
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 19
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 Smile
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87284 Visits: 41113
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 Smile


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. :-P 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
vince_sql
vince_sql
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 264
You should update from 'Part' then join the other two tables.

But Jeff's answer is much better :-D

http://sqlvince.blogspot.com/
ghanshyam.kundu
ghanshyam.kundu
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 435
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
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14325 Visits: 15977
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
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87284 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87284 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14325 Visits: 15977
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
mwendeck
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 19
Thanks for all the information you have provided to me. Very Helpful! Thanks Again!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search