Update rows using values from another table

  • I have master





    a212, Painting






    I have 2nd temp table





    a212, <?xml version 1.0?><root><job jobno 720 /></root>

    a213, <?xml version 1.0?><root><job jobno 721 /></root>

    a214, <?xml version 1.0?><root><job jobno 722 /></root>

    a215, <?xml version 1.0?><root><job jobno 723 /></root>

    after some processing the temporary table column jobxml contains a small XML document with the jobno for each row in tblJob.

    I would like to update tblJob(jobno) field with the jobno value from the XML column #tmpJob(jobxml) when i join both tables.

    Will a single update statement be able to do this and a script to do this, please.

  • Yes, it will. First, make sure you don't have any duplicate jobids in the second table so that you don;t get unpredictable results, then use UPDATE...FROM to join and update in one go, joining on jobid. You'll need a bit of XQuery to shred the XML as well. Sorry, but I haven't got the time or the XQuery knowledge to write the script for you.


  • Your XML isn't valid, but this is roughly what you want

    update t1

    set jobno = t2.jobxml.value('(/root/job/@jobno)[1]','int')

    from tblJob t1

    inner join #tmpJob t2 on t2.jobid = t1.jobid


    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum


Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply