• Eirikur Eiriksson - Saturday, May 26, 2018 1:35 AM

    montserrat.deza - Friday, May 25, 2018 6:02 PM

    Hello could you help me to write this query? I need to update from package.serialnumber the values of package_backup serialnumber. 

    They are same tables with same structure but not sure why the following query is not working. Could you help?

    Thank you!

    UPDATE
        package
    SET
        serialnumber = PB.Serialnumber
    FROM
        Package P,package_backup PB   
     where P.id=PB.id and P.PackageStatusId=1;

    Try this
    😎

    UPDATE P
    SET P.serialnumber = PB.Serialnumber
    FROM   Package      P
    INNER JOIN package_backup    PB
    ON    P.id      = PB.id
    AND   P.PackageStatusId = 1;

    One reason your query could be failing is the cross join implied in the from clause, without any further information, there is no way to tell.

    To add to that, you MUST include the target of the update in the FROM clause for joined updates like Eirikur did or you could end up with an update that will usually seem to work just fine and then someday... WHAM!  A 6 second update will suddenly drive several CPUs to the stops and take hours to execute or fail due to a "halloweening-like" actual execution plan that won't show up as being so in an estimated execution plan even if the update doesn't cause the data movement that typical of the true "Halloweening" problem in databases.

    Also, not having the target of the update in the FROM clause for joined updates is actually an illegal and certainly not supported form of UPDATE.  You won't find any joined UPDATE example that doesn't have the target of the update in the FROM clause anywhere in Books Online or other Microsoft generated documentation.,

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)