November 5, 2013 at 2:49 pm
Getting a syntax error on the "WHERE" clause (second last line)...
That clause should modify the main "UPDATE tblBoMDetail" command...
Ideas?
DECLARE
@SourceID int= 0313-- Denotes the BoMID of the Source BoM (Must exist)
,@TargetID int= 0661-- Denotes the BomID of the Target (Reciever) BoM (Must exist), When "0", means create "Favorite"
,@SumDuplicates int= 0-- When 0 (default) means Don't Copy Duplicates, When 1, Add quantities on duplicates
UPDATE tblBoMDetail
SET D0.BoMItemQty = S.SumItemQty
FROM
tblBoMDetail D0
JOIN
(SELECT S1.BoMItemID
, (S1.BoMEItemQty + isnull(T1.BoMEItemQty,0)) as SumItemQty
FROM tblBoMDetail S1
left join tblBoMDetail T1
ON S1.BoMItemID=T1.BoMItemID
WHERE S1.BoMID = @SourceID AND T1.BoMID = @TargetID) S
JOIN
tblItemMaster M
ON (S.BoMItemID = M.ItemID)
WHERE S.BoMID = @SourceID
and M.ItemStatus = 1-- Active
Jim
November 5, 2013 at 3:47 pm
You do not specify what to join D0 to S on. I think you need something like this:
WHERE S1.BoMID = @SourceID AND T1.BoMID = @TargetID) S on D0.BoMitemID = s.BoMitemID
November 6, 2013 at 5:57 am
Thank you, Keith. That fixed the syntax error. I fixed a couple more that showed up then, now all I have to do is get it to do what I want it to do...;)
BTW... this is the "UP" part of an "Upsert" effort....
Thanks again, Keith!
Jim
November 6, 2013 at 6:03 am
i suspect with large amount of data ..your query might get blocked ..
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 6, 2013 at 6:14 am
Interesting...
...What's a large amount of data? In this instance, there are maybe 20,000 rows in each table, maybe 100 rows per BoMID in tblBoMDetail. On average, this query would probably update less than 2 rows....
...What does "blocked" mean? Do you mean some sort of deadly embrace? I'm not sure how to react....
Jim
November 6, 2013 at 6:26 am
Blocked simply means waiting for a lock that some other process has. Not a deadlock, not fatal. It's not something you can tell from just an update statement, identifying that a statement will or will not be blocked requires a lot more information than what's been posted here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 6:27 am
JimS-Indy (11/6/2013)
What does "blocked" mean? Do you mean some sort of deadly embrace? I'm not sure how to react....
i have experinced it in recent times. that same table is being excess for scanning the data plus used for insert/updating in same tranaction also internal sql work table is get created as it is happeing in your case too (tblBoMDetail getting used 2 times for scanning plus 3rd time for updation).
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 18, 2013 at 10:35 pm
Here's a good intro article to dispel myths about Locking, Blocking and Deadlocking [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply