CODEWITHSUNDEEP
sqlsql-servert-sql
user6305775
user6305775

Reputation: 57

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

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

enter image description here

Here is the data looks like in the Application

enter image description here

Here is the record in the MIITEM or Destination tableenter image description here

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&quot;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

Upvotes: 0

Views: 96

Answers (1)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

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

Upvotes: 3

Related Questions