Update rows using values from another table
-
I have master
tblJob
(jobid,jobdesc,jobno)
Data
-----
a212, Painting
a213,Plastering
a214,windows
a215,Guttering
a216,Gardening
a217,Clearing
I have 2nd temp table
#tmpJob
(jobid,jobxml)
Data
----
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.
John
-
Your XML isn't valid, but this is roughly what you want
update t1set jobno = t2.jobxml.value('(/root/job/@jobno)[1]','int')from tblJob t1inner 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
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply