蛙蛙推荐:使用XML字段批量更新数据库

摘要:有时候我们想根据不同的where条件更新多条记录,一般就需要用多条语句,本文教你怎样用一次查询完成,但XML处理会让数据库进程CPU升高,请酌情使用。

 

--1、初始化临时表及原始数据
if object_id('tempdb..#t'is not null
   
drop table #t
if object_id('tempdb..#t2'is not null
   
drop table #t2

create table #t(k varchar(100),v int)
insert into #t values('a',1)
insert into #t values('b',2)
select * from #t
--输出如下
--
k    v
--
a-1
--
b-2

--2、准备批量更新的XML
DECLARE @a TABLE(data XML)
INSERT @a SELECT '
<root>
    <i k=
''a'' v=''3'' />
    <i k=
''b'' v=''4'' />
</root>
'



--3、将表变量a输出到一个临时结果集,并用来和原始表#t join后更改原始表
--
这里用到了带有from子句的update语句和cte类型
update #t set #t.v = d.v
from #t inner join (
    
SELECT b.id k,c.id v FROM @a a
    
CROSS APPLY
    (
        
SELECT id = t.x.value('@k','varchar(100)'FROM a.data.nodes('//i'AS t(x)
    ) b
    
CROSS APPLY
    (
        
SELECT id = t2.x.value('@v','int'
        
FROM a.data.nodes('//i'AS t2(x)
        
where t2.x.value('@k','varchar(100)'= b.id
    ) c
as d
on #t.k = d.k
--其中cte d的结果集应该如下
--
k    v
--
a-3
--
b-4

--4、验证原始表是否已经修改
select * from #t
--输出如下
--
k    v
--
a-3
--
b-4
posted @ 2008-12-29 18:29  蛙蛙王子  Views(3326)  Comments(22Edit  收藏  举报