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

[杨莱珏]

我想更新表中某一列中的部分字段，根据关联用其它表的的内容代替，该怎么做？？
举例如下：
表一中某列有五条记录值分别是：（列名：LCDYZD_GSX）
KMJE(-1,0,5402,JFFS,0)
KMJE(0,0,5402,JFLJ,0)
KMJE(-1,0,5102,JFFS,0)-KMJE(-1,0,5405,JFFS,0)
KMJE(-1,0,5502,JFFS,0)
KMJE(0,0,6681021,DFFS,0)
表二中二列给出的与表一对应关系：(列名：F_YKM F_XKM)
5402 1101
5102 2301
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,5405,JFFS,0)
KMJE(-1,0,1414,JFFS,0)
KMJE(0,0,2304451,DFFS,0)

其实表中数据很多，手工量太大，这是我截取的，请朋友们帮帮我吧，多谢！！

[-狙击手-]

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

[杨莱珏]

对了表二还有一条对应的现补上就该是这样的：
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)

[loworth]

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

[杨莱珏]

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更新，其它的不动

[loworth]

你试了吗？你看到结果了吗？你就说全更新了！！！
我那不是已经有限制条件了！

[loworth]

你的表结构是什么样的？ 估计我的理解跟你的说法有误

[loworth]

我好像稍微明白点了
你这个表原来连第一范式都不满足

可试以下查询，
[CODE=SQL]
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
[/CODE]
前提是[表1].[LCDYZD_GSX]字段里不会出现 KMJE(5402,0,5402,54025402) 之类的数据 否则 我无能为力了

[ORARichard]

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+',')>0

select * 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)

*/

[杨莱珏]

8楼的写的很好，可我给出的例子只是表中记录的一部分，其实有1000多条，要是这么少的话我在表里直接就改了。

[ORARichard]

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)
*/

[ORARichard]

没看懂9楼的意思，不管你有多少记录，这种方法都适用阿

[jackylee918]

update 表一
set lcdyzd_gsx=replace(lcdyzd_gsx,表二.f_ykm,表二.f_xkm)
from 表一,表二
where 表一.id=表二.id


9楼，rtrim是不是有点多此一举呢？ :)