更新某列中部分内容，很难，求朋友帮忙解决

Question

update a
set lcdyzd_gsx = replace(lcdyzd,b.f_ykm,b.f_xkm)
from table1 a
left join table2 on a.主键 = b.主键

Answer 1

对了表二还有一条对应的现补上就该是这样的：
5402               1101 
5102               2301 
5405               2401
5502               1414 
6681021         2304451更新完是这样的：KMJE(-1,0,1101,JFFS,0) 
KMJE(0,0,1101,JFLJ,0) 
KMJE(-1,0,2301,JFFS,0)-KMJE(-1,0,2401,JFFS,0) 
KMJE(-1,0,1414,JFFS,0) 
KMJE(0,0,2304451,DFFS,0) 

Answer 2

UPDATE [表1]
SET [表1].[LCDYZD_GSX]=[表2].[F_XKM]
FROM
[表2]
WHERE
[表1].[LCDYZD_GSX]=[表2].[F_YKM] 

Answer 3

UPDATE [表1]
SET [表1].[LCDYZD_GSX]=[表2].[F_XKM]
FROM
[表2]
WHERE
[表1].[LCDYZD_GSX]=[表2].[F_YKM] ----------------------------------------
这么做不是把LCDYZD_GSX都更新了吗？？而不是更新一部分，我只想把其中的科目，如：KMJE(-1,0,5402,JFFS,0) 中的5402更新，其它的不动

Answer 4

我好像稍微明白点了
你这个表原来连第一范式都不满足可试以下查询，UPDATE [表1]
SET [表1].[LCDYZD_GSX]=REPLACE([表1].[LCDYZD_GSX],[表2].[F_YKM],[表2].[F_XKM])
FROM
[表2]
WHERE
CHARINDEX([表1].[LCDYZD_GSX],[表2].[F_YKM])>0前提是[表1].[LCDYZD_GSX]字段里不会出现 KMJE(5402,0,5402,54025402) 之类的数据 否则 我无能为力了

Answer 5

declare @t1 table(LCDYZD_GSX varchar(100))
insert @t1
select '-1,0,5402,JFFS,0' union all 
select '0,0,5402,JFLJ,0' union all 
select '-1,0,5102,JFFS,0' union all
select '-1,0,5405,JFFS,0' union all 
select '-1,0,5502,JFFS,0' union all 
select '0,0,6681021,DFFS,0' 
declare @t2 table(F_YKM int,F_XKM int)
insert @t2 
select 5402,               1101 union all
select 5102,               2301 union all
select 5502,               1414 union all
select 6681021,         2304451 update t1 set lcdyzd_gsx=replace(lcdyzd_gsx,','+rtrim(f_ykm)+',',','+rtrim(f_xkm)+',')
from @t1 t1 inner join @t2 t2 on charindex(','+rtrim(f_ykm)+',',','+lcdyzd_gsx+',')>0select * from @t1/*
LCDYZD_GSX
----------------------------------------------------------------------------------------------------
-1,0,1101,JFFS,0
0,0,1101,JFLJ,0
-1,0,2301,JFFS,0
-1,0,5405,JFFS,0
-1,0,1414,JFFS,0
0,0,2304451,DFFS,0(6 row(s) affected)*/

Answer 6

declare @t1 table(LCDYZD_GSX varchar(100))
insert @t1
select 'KMJE(-1,0,5402,JFFS,0)' union all 
select 'KMJE(0,0,5402,JFLJ,0)' union all 
select 'KMJE(-1,0,5102,JFFS,0)-KMJE(-1,0,5405,JFFS,0)' union all
select 'KMJE(-1,0,5502,JFFS,0)' union all 
select 'KMJE(0,0,6681021,DFFS,0)' declare @t2 table(F_YKM int,F_XKM int)
insert @t2 
select 5402,1101 union all
select 5102,2301 union all
select 5405,2401 union all
select 5502,1414 union all
select 6681021,2304451 while(@@rowcount>0)
update t1 set LCDYZD_GSX=replace(LCDYZD_GSX,','+rtrim(f_ykm)+',',','+rtrim(f_xkm)+',') from  @t1 t1 inner join @t2 t2
on charindex(','+rtrim(f_ykm)+',',','+lcdyzd_gsx+',')>0
select * from @t1/*
LCDYZD_GSX
----------------------------------------------------------------------------------------------------
KMJE(-1,0,1101,JFFS,0)
KMJE(0,0,1101,JFLJ,0)
KMJE(-1,0,2301,JFFS,0)-KMJE(-1,0,2401,JFFS,0)
KMJE(-1,0,1414,JFFS,0)
KMJE(0,0,2304451,DFFS,0)(5 row(s) affected)
*/

Answer 7

update 表一
set lcdyzd_gsx=replace(lcdyzd_gsx,表二.f_ykm,表二.f_xkm)
from 表一,表二
where 表一.id=表二.id
9楼，rtrim是不是有点多此一举呢？  :)