Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

UPDATE Check Expand / Collapse
Author
Message
Posted Monday, September 26, 2011 6:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 13, 2011 8:03 PM
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 :)
Post #1181438
Posted Monday, September 26, 2011 7:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 36,773, Visits: 31,229
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1181447
Posted Monday, September 26, 2011 8:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 10:31 AM
Points: 44, Visits: 263
You should update from 'Part' then join the other two tables.

But Jeff's answer is much better


http://sqlvince.blogspot.com/
Post #1181448
Posted Tuesday, September 27, 2011 1:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, 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
Post #1181529
Posted Tuesday, September 27, 2011 2:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 5,233, Visits: 9,463
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
Post #1181563
Posted Tuesday, September 27, 2011 6:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 36,773, Visits: 31,229
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1181665
Posted Tuesday, September 27, 2011 6:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 36,773, Visits: 31,229
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1181666
Posted Tuesday, September 27, 2011 6:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 5,233, Visits: 9,463
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
Post #1181687
Posted Tuesday, September 27, 2011 5:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 13, 2011 8:03 PM
Points: 3, Visits: 19
Thanks for all the information you have provided to me. Very Helpful! Thanks Again!
Post #1182125
Posted Tuesday, September 27, 2011 6:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:15 PM
Points: 1,945, Visits: 2,861
>> am sing the Update statement correctly here? <<

No, this is dialect. And you did not post any DDL. 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. Her is my guess from your narratives. What you have describes is a horrible design with lots of attribute splittings. Parts are the elements of an inventory set. The cost, the supplier and unit price should be in this table.

CREATE TABLE Inventory
(part_nbr CHAR(13) NOT NULL PRIMARY KEY,
supplier_duns CHAR(10) NOT NULL
REFERENCES Suppliers(supplier_duns),
onhand_qty INTEGER NOT NULL,
CHECK (onhand_qty >= 0),
part_unit_cost DECIMAL (12,2) DEFAULT 0.00 NOT NULL,
part_unit_price DECIMAL (12,2) DEFAULT 0.00 NOT NULL,
..);

If you get all of the attributes in the same table, would it look more like this skeleton?

MERGE INTO Inventory AS I
USING Vendor_Pricelist AS VP
ON I.part_nbr = VP. part_nbr
AND I. supplier_duns = VP.supplier_duns
WHEN MATCHED
UPDATE SET I.part_unit_cost = VP.part_unit_cost;


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1182141
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse