TSQL: How can i Insert variable and text into the a single field at a time

Question

I have the following code to update records in MIITEM which works fine, But how can i use data from another table called [WI] dynamically

use DB1;
Go
update MIITEM
set [fldXml]= '<fields>
<field5>USE DISC:300230 FORMED OD:13.48 BH SPEC:8/8/6.003, BH Size: 0.656, C/S Spec: 90/0.843/CONICAL 2</field5>
<field6>1 - CL THRU PLATE-V/S HOLE SIZE:5/8&quot;Z1 V:0.125 L:0.125  COLOR:100270 AGC REVERSE DISC</field6>
<field7>N/A</field7>
</fields>'
where [itemId]='500201'

here is the data looks like in Source table

Here is the data looks like in the Application

Here is the record in the MIITEM or Destination table

I want to replace the text USE DISC:300230 FORMED OD:13.48 BH SPEC:8/8/6.003, BH Size: 0.656, C/S Spec: 90/0.843/CONICAL 2 by the value in [wi].[DISC_NOTE] field in [WI] table dynamically

I want to replace 1 - CL THRU PLATE-V/S HOLE SIZE:5/8"Z1 V:0.125 L:0.125 COLOR:100270 AGC REVERSE DISC by the value [wi].[WHEEL_NOTE] field dynamically

I want replace N/A [wi].[ASSEMBLY_NOTE] field from [WI] table. to go between field dynamically

Possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Sean Lange, Aug 01 '16 at 18:11
@SeanLange I dont think its duplicate, mine is different issue, I am looking to update `fldXml` field by adding records from `whl` table into ``, ``, ``. `````` are constant — user6305775, Aug 01 '16 at 18:16
Right...which is exactly what the other question is doing. You can use joins in your update statements with sql server. — Sean Lange, Aug 01 '16 at 18:18

Alex Kudryashev · Accepted Answer · 2016-08-02T00:32:42.940

You can use TSQL XML DML for this purpose (assuming fldXml is xml data type). The query looks like this.

update MIITEM
set [fldXml].modify('replace value of (fields/field5)[1]
                     with sql:column("DISC_NOTE")')
from MIITEM m 
     inner join wi on m.idemid=wi.stock_id
--where m.idemid='500201' --if you need it

Note that you have to run update trice (separately for each field5, field6 and field7) because XML DML doesn't allow multiple node update.

update

If fldXml is a string then you need to compare overhead between building whole string

fldXml='<fields><field5>'+wi.disc_note+...

and creating temporary table.

declare @MIITEM table (itemid int, fldXml xml)
insert @MIITEM (itemid, fldXml)
from MIITEM 
--where... if you need

then update @MIITEM several times for each node according to initial answer and finally

update MIITEM
set [fldXml]=x.fldXml 
from MIITEM t
     inner join @MIITEM x on t.itemid = x.itemid

If `fldXml` is varchar then you can build string like `fldXml=''+wi.disc_note+...` — Alex Kudryashev, Aug 01 '16 at 19:04

TSQL: How can i Insert variable and text into the a single field at a time

1 Answers1

update