Update rows using values from another table

Question

Update rows using values from another table

fafful

SSC Eights!

Points: 810
More actions
July 22, 2016 at 7:10 am

#400453

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.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

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

http://www.sqlservercentral.com/articles/Best+Practices/61537