表结构:
表: pro_eccodeorgid eccode
21 22323232
23 11111111
表: usr_org orgid branchid
21 100
23 200
我想将pro_eccode表根据orgid字段 到usr_org表 关联取出branchid,关将eccode 的前三位值更新成branchid的值.
一条sql如何搞定?
表: pro_eccodeorgid eccode
21 22323232
23 11111111
表: usr_org orgid branchid
21 100
23 200
我想将pro_eccode表根据orgid字段 到usr_org表 关联取出branchid,关将eccode 的前三位值更新成branchid的值.
一条sql如何搞定?
set eccode=stuff(eccode,1,3,branchid)
from pro_eccode ,usr_org
where pro_eccode.orgid=usr_org.orgid
from pro_eccode a
inner join usr_org b
on a.orgid=b.orgid
eccode = stuff(a.eccode ,1,3,b.branchid)
from pro_eccode a, usr_org b
where a.orgid=b.orgid
update a
set a.eccode = stuff(a.eccode,1,3,isnull(b.branchid,''))
from pro_eccode a
left join usr_org b
on b.orgid = a.orgid
update pro_eccode
set eccode=stuff(eccode,1,3,usr_org.branchid)
from usr_org
where pro_eccode.orgid=usr_org.orgidSelect * from pro_eccode
21 10023232
23 20011111
insert into ta values('21' , '22323232')
insert into ta values('23' , '11111111')
create table tb(orgid varchar(20) , branchid varchar(20))
insert into tb values('20' , '100')
insert into tb values('23' , '200')
goupdate ta set eccode = n.branchid + substring(m.eccode , 3 , len(m.eccode)) from ta m , tb n where m.orgid = n.orgidselect * from tadrop table ta , tb/*
orgid eccode
-------------------- --------------------
21 22323232
23 200111111(所影响的行数为 2 行)
*/
insert into ta values('21' , '22323232')
insert into ta values('23' , '11111111')
create table tb(orgid varchar(20) , branchid varchar(20))
insert into tb values('21' , '100')
insert into tb values('23' , '200')
goupdate ta set eccode = n.branchid + substring(m.eccode , 3 , len(m.eccode)) from ta m , tb n where m.orgid = n.orgidselect * from tadrop table ta , tb/*
orgid eccode
-------------------- --------------------
21 100323232
23 200111111(所影响的行数为 2 行)
*/
刪除指定的字元長度並於指定的開始點插入另一字元集。語法
STUFF ( character_expression , start , length , character_expression ) 本範例傳回藉由從第一個字串 (abcdef) 位置 2開始 (於 b) 刪除三個字元,並於刪除點插入第二個字串所建立的字元字串。 SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO以下為結果集:---------
aijklmnef (1 row(s) affected)
create table ta(orgid varchar(20) , eccode nvarchar(20))
insert into ta values('21' , '22323232')
insert into ta values('23' , '11111111')
create table tb(orgid varchar(20) , branchid nvarchar(20))
insert into tb values('21' , '100')
insert into tb values('23' , '200')
goupdate ta set eccode = nvl((select branchid from tb where orgid = ta.orgid) + substr(eccode , 3 , len(eccode)),eccode)select * from tadrop table ta , tb
create table ta(orgid varchar(20) , eccode varchar2(20))
insert into ta values('21' , '22323232')
insert into ta values('23' , '11111111')
create table tb(orgid varchar(20) , branchid varchar2(20))
insert into tb values('21' , '100')
insert into tb values('23' , '200')
goupdate ta set eccode = nvl((select branchid from tb where orgid = ta.orgid) + substr(eccode , 3 , len(eccode)),eccode)select * from tadrop table ta , tb
insert into ta values('21' , '22323232')
insert into ta values('23' , '11111111')
create table tb(orgid varchar(20) , branchid varchar2(20))
insert into tb values('21' , '100')
insert into tb values('23' , '200')
update ta set eccode = nvl((select branchid from tb where orgid = ta.orgid) + substr(eccode , 3 , length(eccode)),eccode)select * from tadrop table ta , tb
set a.eccode = CONCAT (b.branchid ,SUBSTR(a.eccode, 3,length(a.eccode) - 3))
from pro_eccode a
join usr_org b
on b.orgid = a.orgid
create table ta(orgid varchar2(20) , eccode varchar2(20))
insert into ta values('21' , '22323232')
insert into ta values('23' , '11111111')
create table tb(orgid varchar2(20) , branchid varchar2(20))
insert into tb values('21' , '100')
insert into tb values('23' , '200')update ta set eccode = nvl((select to_char(branchid) from tb where orgid = ta.orgid) || substr(eccode , 4 , length(eccode)),eccode)select * from tadrop table ta , tb/*
ORGID ECCODE
-------------------- --------------------
21 323332
23 111311 2 rows selected.*/
declare @a table (orgid int,eccode int)
insert into @a select 21,22323232
union all select 23,11111111
declare @b table (orgid int,branchid int)
insert into @b select 21,100
union all select 23,200
----------------
update a set a.eccode=stuff(a.eccode,1,3,b.branchid) from @a a join @b b on a.orgid=b.orgid
select * from @a
from pro_eccode a
inner join usr_org b
on a.orgid=b.orgid