T-SQL syntax

  • 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

  • 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



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • 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

  • 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;-)

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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;-)

  • Here's a good intro article to dispel myths about Locking, Blocking and Deadlocking [/url]


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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