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

杨莱珏 2008-01-26 01:53:10
我想更新表中某一列中的部分字段,根据关联用其它表的的内容代替,该怎么做??
举例如下:
表一中某列有五条记录值分别是:(列名: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)

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

...全文
106 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
jackylee918 2008-01-27
  • 打赏
  • 举报
回复
update 表一
set lcdyzd_gsx=replace(lcdyzd_gsx,表二.f_ykm,表二.f_xkm)
from 表一,表二
where 表一.id=表二.id


9楼,rtrim是不是有点多此一举呢? :)
ORARichard 2008-01-26
  • 打赏
  • 举报
回复
没看懂9楼的意思,不管你有多少记录,这种方法都适用阿
ORARichard 2008-01-26
  • 打赏
  • 举报
回复
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)
*/
杨莱珏 2008-01-26
  • 打赏
  • 举报
回复
8楼的写的很好,可我给出的例子只是表中记录的一部分,其实有1000多条,要是这么少的话我在表里直接就改了。
ORARichard 2008-01-26
  • 打赏
  • 举报
回复
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)

*/
loworth 2008-01-26
  • 打赏
  • 举报
回复
我好像稍微明白点了
你这个表原来连第一范式都不满足

可试以下查询,
[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) 之类的数据 否则 我无能为力了
loworth 2008-01-26
  • 打赏
  • 举报
回复
你的表结构是什么样的? 估计我的理解跟你的说法有误
loworth 2008-01-26
  • 打赏
  • 举报
回复
你试了吗?你看到结果了吗?你就说全更新了!!!
我那不是已经有限制条件了!
杨莱珏 2008-01-26
  • 打赏
  • 举报
回复
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 2008-01-26
  • 打赏
  • 举报
回复
[CODE=SQL]
UPDATE [表1]
SET [表1].[LCDYZD_GSX]=[表2].[F_XKM]
FROM
[表2]
WHERE
[表1].[LCDYZD_GSX]=[表2].[F_YKM]
[/CODE]
杨莱珏 2008-01-26
  • 打赏
  • 举报
回复
对了表二还有一条对应的现补上就该是这样的:
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)
-狙击手- 2008-01-26
  • 打赏
  • 举报
回复
update a
set lcdyzd_gsx = replace(lcdyzd,b.f_ykm,b.f_xkm)
from table1 a
left join table2 on a.主键 = b.主键

22,299

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧