February 7, 2012 at 9:33 am
Hi,
I have two temp tables
#TableA ( PKID INT ,KEY char(18), TaxA decimal(6,2) ,Discount decimal(6,2) ,Surcharge decimal(6,2))
#TableB ( PKID INT ,KEY char(18), TaxB decimal(6,2) , Discount decimal(6,2) ,Surcharge decimal(6,2))
I need to update the main table products based on these two temp tables
UPDATE P
SET P.Cost = P.OrigCost - ISNULL(TaxA,0) - ISNULL(TaxB,0) - ISNULL(Discount,0) +ISNULL( Surcharge,0)
P.Price = CASE WHEN ISNULL(P.Quantity,0) = 0 THEN 0 ELSE (P.OrigCost - ISNULL(P.Tax1,0) - ISNULL(P.Tax2,0)) / P.Quantity END
FROM [dbo].[Products] P
WHERE RecordKey IN ( SELECT RecordKey FROM #TableA UNION
SELECT RecordKey FROM #TableB)
while updating the products table , the formula should be like the above
I need to use ISNULL(Discount,0) and ISNULL(Surcharge,0) , ISNULL(TaxA,0) , ISNULL(TaxB,0) from the two temp tables .
Please help .
February 7, 2012 at 9:59 am
What is the question here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2012 at 10:53 am
Use a JOIN instead of an IN. Something like:
UPDATE P
SET P.Cost = P.OrigCost - ISNULL(TaxA,0) - ISNULL(TaxB,0) - ISNULL(Discount,0) +ISNULL( Surcharge,0),
P.Price = CASE
WHEN ISNULL(P.Quantity,0) = 0 THEN 0
ELSE (P.OrigCost - ISNULL(P.Tax1,0) - ISNULL(P.Tax2,0)) / P.Quantity
END
FROM [dbo].[Products] P
LEFT JOIN #TableA A
ON A.RecordKey = P.RecordKey
LEFT JOIN #TableB B
ON B.RecordKey = P.RecordKey
WHERE A.RecordKey IS NOT NULL
OR B.RecordKey IS NOT NULL
February 7, 2012 at 11:50 pm
CELKO (2/7/2012)
Good SQL programmers do not use temp table;we use COALESCE() not ISNULL
Also, we would tend to use a VIEW and not an UPDATE.
And we never, never use the UPDATE ..FROM.. syntax
Opinions vary on these points. I for one disagree with all of them! 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 7:22 am
CELKO (2/7/2012)
And we never, never use the UPDATE ..FROM.. syntax; it is not only proprietary, it does not work right.
I have used this plenty over the years and have never encountered any issues. Can you provide an example or some documentation that this does not work correctly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 7:31 am
Here's where Joe explained it to me before. I now try to avoid UPDATE...FROM unless there is no possibility of cardinality errors - and then I document it in case somebody else is tempted to follow my example in a less appropriate situation. As you'll see from the discussion, my opinion is that proprietary is fine, but does not work right is not.
John
February 8, 2012 at 7:40 am
He's referring to the possibility of non-deterministic updates:
DECLARE @T1 AS TABLE (col1 integer NULL, col2 integer NULL);
DECLARE @T2 AS TABLE (col1 integer NULL, col2 integer NULL);
INSERT @T1 VALUES (1, NULL);
INSERT @T2 VALUES (1, 1), (1, 2);
UPDATE t1
SET col2 = t2.col2
FROM @T1 AS t1
JOIN @T2 AS t2 ON
t2.col1 = t1.col1;
-- Should col2 = 1 or 2?
SELECT * FROM @T1 AS t1;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2012 at 8:02 am
John Mitchell-245523 (2/8/2012)
I now try to avoid UPDATE...FROM unless there is no possibility of cardinality errors - and then I document it in case somebody else is tempted to follow my example in a less appropriate situation. As you'll see from the discussion, my opinion is that proprietary is fine, but does not work right is not.
It's a judgement call of course. My personal preference is for UPDATE FROM over MERGE in most cases, unless I am genuinely doing something that requires mixed INSERT/UPDATE/DELETE behaviour. The problem of writing code that only updates or deletes the same target row once is something I can live with: the alternative being the sometimes appallingly poor performance of MERGE.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply