update [tb] set [TaskIDs]=a.ID from test a where a.[GUID]=tb.[GUID] and [tb].ProjectID=14 and a.ProjectID=6with t as( select * from test where [ProjectID]=14 ) update tb set TaskIDs=t.ID from t where t.[GUID]=tb.[GUID] and tb.ProjectID=6--try
update PMS_TaskAllocation set [TaskIDs]=a.ID from PMS_Task a where a.[GUID]=PMS_TaskAllocation.[GUID] and PMS_TaskAllocation.ProjectID=14 and a.ProjectID=6with t as( select * from PMS_Task where [ProjectID]=14 ) update PMS_TaskAllocation set TaskIDs=t.ID from t where t.[GUID]=PMS_TaskAllocation.[GUID] and PMS_TaskAllocation.ProjectID=6
是我没有表达清楚,不过根据你写的SQL,让我有了点思路,但是现在还是有一个问题: WITH t AS( SELECT a.ID AS aID,b.id AS bID, b.bGUID FROM ( SELECT DISTINCT t.guid, t.ID, ta.guid AS bGUID FROM dbo.PMS_Task t, dbo.PMS_TaskAllocation ta WHERE CHARINDEX(','+LTRIM(t.id)+',',','+ta.TaskIDs+',') > 0 and t.projectid = 6 ) b, ( SELECT guid, ID FROM dbo.PMS_Task WHERE projectid = 14 ) a WHERE b.guid = a.guid ) update PMS_TaskAllocation SET TaskIDs = replace(TaskIDs,t.bID,t.aID) FROM t WHERE t.[bGUID]=PMS_TaskAllocation.[GUID] and PMS_TaskAllocation.ProjectID = 14我是用替换写的,最后得到的结果这条有问题(我想是没有完全替换): 62AC266E-6567-44E8-8FD6-D30C1C5B2708 74,33,40 14请问怎么才能完全替换呢
if OBJECT_ID('PMS_TaskAllocation') is not null drop table PMS_TaskAllocation create table PMS_TaskAllocation( GUID uniqueidentifier, TaskIDs nvarchar(20), ProjectID int )insert into PMS_TaskAllocation select 'BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A','34',6 union all select '2A2BE50E-301C-489A-8E91-60158EB81269','35',6 union all select '62AC266E-6567-44E8-8FD6-D30C1C5B2708','32,33,40',6 union all select '09C88927-24B6-4AC8-BA97-2C175EB92558','36', 6 union all select 'B9F70F43-3CEC-4FDD-837F-BF33B7935AF4','41',6 union all select '8084E143-F249-4BBC-8698-CBE67AE420D5','31',6 union all select 'BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A','34',14 union all select '2A2BE50E-301C-489A-8E91-60158EB81269','35',14 union all select '62AC266E-6567-44E8-8FD6-D30C1C5B2708','32,33,40',14 union all select '09C88927-24B6-4AC8-BA97-2C175EB92558','36',14 union all select 'B9F70F43-3CEC-4FDD-837F-BF33B7935AF4','41',14 union all select '8084E143-F249-4BBC-8698-CBE67AE420D5','31',14 if OBJECT_ID('PMS_Task') is not null drop table PMS_Task create table PMS_Task( ID int, GUID uniqueidentifier, ProjectID int )insert into PMS_Task select 30,'6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1',6 union all select 31,'4EDE656D-E05B-4745-B816-2E6DF042FD7B',6 union all select 32,'1264B5B3-9D48-4CB7-BF15-57B81A9EB79D',6 union all select 33,'D2A7B904-F733-42F9-B693-31ABFC5F6DF1',6 union all select 34,'C3E2B888-1DCB-49C3-B1C4-5D96195CD057',6 union all select 35,'E34618A2-C2A8-483B-AD17-FC32BEFD8D87',6 union all select 36,'B2A3CEB7-CEC1-4A69-9150-A2FE30513211',6 union all select 37,'6DF78928-00F1-4725-92E2-E9665C11CEA7',6 union all select 38,'B9913A50-637B-473D-91A0-FEBBA3E89E44',6 union all select 40,'591F409D-7F63-4494-8D18-40DEF8AF7C99',6 union all select 41,'78A2409A-3E6B-414F-85B2-DAEAFFE5E913',6 union all select 42,'23041727-869D-4B6D-8A62-8F8128FB70D1',6 union all select 70,'6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1',14 union all select 71,'4EDE656D-E05B-4745-B816-2E6DF042FD7B',14 union all select 72,'78A2409A-3E6B-414F-85B2-DAEAFFE5E913',14 union all select 73,'23041727-869D-4B6D-8A62-8F8128FB70D1',14 union all select 74,'1264B5B3-9D48-4CB7-BF15-57B81A9EB79D',14 union all select 75,'D2A7B904-F733-42F9-B693-31ABFC5F6DF1',14 union all select 76,'C3E2B888-1DCB-49C3-B1C4-5D96195CD057',14 union all select 77,'E34618A2-C2A8-483B-AD17-FC32BEFD8D87',14 union all select 78,'B2A3CEB7-CEC1-4A69-9150-A2FE30513211',14 union all select 79,'6DF78928-00F1-4725-92E2-E9665C11CEA7',14 union all select 80,'B9913A50-637B-473D-91A0-FEBBA3E89E44',14 union all select 81,'591F409D-7F63-4494-8D18-40DEF8AF7C99',14 declare @str nvarchar(20) declare @tb table(TaskIDs nvarchar(50),ID nvarchar(10)) set @str=''; with t1 as( select TaskIDs,b.GUID bGUID from PMS_TaskAllocation a join PMS_Task b on CHARINDEX(convert(nvarchar(10),b.ID),a.TaskIDs,1)<>0 where a.ProjectID=14 ), t2 as( select TaskIDs,b.ID from t1 a join PMS_Task b on a.bGUID=b.GUID and b.ProjectID=14 ) insert into @tb select TaskIDs,ID from t2select @str+=convert(nvarchar(10),ID)+',' from @tb where ID in(74,75,81) set @str=LEFT(@str,len(@str)-1) ---以下是更新表PMS_TaskAllocation的TaskIDs字段的语句 update PMS_TaskAllocation set TaskIDs=t.ID from ( select * from @tb where ID not in(74,75,81) union all select '32,33,40',@str ) t where ProjectID=14 and t.TaskIDs=PMS_TaskAllocation.TaskIDs
这就是应该得出的结果
GUID TaskIDs ProjectID
------------------------------------------- -------------------- -----------
BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A 34 6
2A2BE50E-301C-489A-8E91-60158EB81269 35 6
62AC266E-6567-44E8-8FD6-D30C1C5B2708 32,33,40 6
09C88927-24B6-4AC8-BA97-2C175EB92558 36 6
B9F70F43-3CEC-4FDD-837F-BF33B7935AF4 41 6
8084E143-F249-4BBC-8698-CBE67AE420D5 31 6
BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A 76 14
2A2BE50E-301C-489A-8E91-60158EB81269 77 14
62AC266E-6567-44E8-8FD6-D30C1C5B2708 74,75,81 14
09C88927-24B6-4AC8-BA97-2C175EB92558 78 14
B9F70F43-3CEC-4FDD-837F-BF33B7935AF4 72 14
8084E143-F249-4BBC-8698-CBE67AE420D5 71 14
TaskIDs 就是 PMS_Task 表中的ID(自增)
PMS_TaskAllocation 中 ProjectID=6的数据是老数据
现在我Copy了一份新数据(ProjectID=14)
这样TaskIDs应该替换成PMS_Task中ProjectID=14的新ID(替换的条件是ProjectID=14的GUID 等于 ProjectID=6的GUID)
update [tb]
set [TaskIDs]=a.ID
from test a
where a.[GUID]=tb.[GUID] and [tb].ProjectID=14 and a.ProjectID=6with t
as(
select * from test where [ProjectID]=14
)
update tb
set TaskIDs=t.ID from t where t.[GUID]=tb.[GUID] and tb.ProjectID=6--try
你要把PMS_TaskAllocation 表中ProjectID=14的数据的
TaskIDs更新为PMS_Task中 ProjectID=6的数据对应的ID,通过GUID来对应
是这个意思么?
update PMS_TaskAllocation
set [TaskIDs]=a.ID
from PMS_Task a
where a.[GUID]=PMS_TaskAllocation.[GUID] and PMS_TaskAllocation.ProjectID=14 and a.ProjectID=6with t
as(
select * from PMS_Task where [ProjectID]=14
)
update PMS_TaskAllocation
set TaskIDs=t.ID from t where t.[GUID]=PMS_TaskAllocation.[GUID] and PMS_TaskAllocation.ProjectID=6
是我没有表达清楚,不过根据你写的SQL,让我有了点思路,但是现在还是有一个问题: WITH t
AS(
SELECT a.ID AS aID,b.id AS bID, b.bGUID
FROM
(
SELECT DISTINCT t.guid, t.ID, ta.guid AS bGUID
FROM dbo.PMS_Task t, dbo.PMS_TaskAllocation ta
WHERE CHARINDEX(','+LTRIM(t.id)+',',','+ta.TaskIDs+',') > 0 and t.projectid = 6
) b,
(
SELECT guid, ID FROM dbo.PMS_Task WHERE projectid = 14 ) a
WHERE b.guid = a.guid
)
update PMS_TaskAllocation
SET TaskIDs = replace(TaskIDs,t.bID,t.aID)
FROM t WHERE t.[bGUID]=PMS_TaskAllocation.[GUID] and PMS_TaskAllocation.ProjectID = 14我是用替换写的,最后得到的结果这条有问题(我想是没有完全替换):
62AC266E-6567-44E8-8FD6-D30C1C5B2708 74,33,40 14请问怎么才能完全替换呢
62AC266E-6567-44E8-8FD6-D30C1C5B2708 74,75,81 14
create table PMS_TaskAllocation(
GUID uniqueidentifier,
TaskIDs nvarchar(20),
ProjectID int
)insert into PMS_TaskAllocation
select 'BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A','34',6 union all
select '2A2BE50E-301C-489A-8E91-60158EB81269','35',6 union all
select '62AC266E-6567-44E8-8FD6-D30C1C5B2708','32,33,40',6 union all
select '09C88927-24B6-4AC8-BA97-2C175EB92558','36', 6 union all
select 'B9F70F43-3CEC-4FDD-837F-BF33B7935AF4','41',6 union all
select '8084E143-F249-4BBC-8698-CBE67AE420D5','31',6 union all
select 'BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A','34',14 union all
select '2A2BE50E-301C-489A-8E91-60158EB81269','35',14 union all
select '62AC266E-6567-44E8-8FD6-D30C1C5B2708','32,33,40',14 union all
select '09C88927-24B6-4AC8-BA97-2C175EB92558','36',14 union all
select 'B9F70F43-3CEC-4FDD-837F-BF33B7935AF4','41',14 union all
select '8084E143-F249-4BBC-8698-CBE67AE420D5','31',14 if OBJECT_ID('PMS_Task') is not null drop table PMS_Task
create table PMS_Task(
ID int,
GUID uniqueidentifier,
ProjectID int
)insert into PMS_Task
select 30,'6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1',6 union all
select 31,'4EDE656D-E05B-4745-B816-2E6DF042FD7B',6 union all
select 32,'1264B5B3-9D48-4CB7-BF15-57B81A9EB79D',6 union all
select 33,'D2A7B904-F733-42F9-B693-31ABFC5F6DF1',6 union all
select 34,'C3E2B888-1DCB-49C3-B1C4-5D96195CD057',6 union all
select 35,'E34618A2-C2A8-483B-AD17-FC32BEFD8D87',6 union all
select 36,'B2A3CEB7-CEC1-4A69-9150-A2FE30513211',6 union all
select 37,'6DF78928-00F1-4725-92E2-E9665C11CEA7',6 union all
select 38,'B9913A50-637B-473D-91A0-FEBBA3E89E44',6 union all
select 40,'591F409D-7F63-4494-8D18-40DEF8AF7C99',6 union all
select 41,'78A2409A-3E6B-414F-85B2-DAEAFFE5E913',6 union all
select 42,'23041727-869D-4B6D-8A62-8F8128FB70D1',6 union all
select 70,'6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1',14 union all
select 71,'4EDE656D-E05B-4745-B816-2E6DF042FD7B',14 union all
select 72,'78A2409A-3E6B-414F-85B2-DAEAFFE5E913',14 union all
select 73,'23041727-869D-4B6D-8A62-8F8128FB70D1',14 union all
select 74,'1264B5B3-9D48-4CB7-BF15-57B81A9EB79D',14 union all
select 75,'D2A7B904-F733-42F9-B693-31ABFC5F6DF1',14 union all
select 76,'C3E2B888-1DCB-49C3-B1C4-5D96195CD057',14 union all
select 77,'E34618A2-C2A8-483B-AD17-FC32BEFD8D87',14 union all
select 78,'B2A3CEB7-CEC1-4A69-9150-A2FE30513211',14 union all
select 79,'6DF78928-00F1-4725-92E2-E9665C11CEA7',14 union all
select 80,'B9913A50-637B-473D-91A0-FEBBA3E89E44',14 union all
select 81,'591F409D-7F63-4494-8D18-40DEF8AF7C99',14 declare @str nvarchar(20)
declare @tb table(TaskIDs nvarchar(50),ID nvarchar(10))
set @str='';
with t1 as(
select TaskIDs,b.GUID bGUID from PMS_TaskAllocation a
join PMS_Task b on CHARINDEX(convert(nvarchar(10),b.ID),a.TaskIDs,1)<>0
where a.ProjectID=14
),
t2 as(
select TaskIDs,b.ID from t1 a
join PMS_Task b on a.bGUID=b.GUID and b.ProjectID=14
)
insert into @tb select TaskIDs,ID from t2select @str+=convert(nvarchar(10),ID)+',' from @tb where ID in(74,75,81)
set @str=LEFT(@str,len(@str)-1)
---以下是更新表PMS_TaskAllocation的TaskIDs字段的语句
update PMS_TaskAllocation set TaskIDs=t.ID from
(
select * from @tb where ID not in(74,75,81)
union all
select '32,33,40',@str
) t
where ProjectID=14 and t.TaskIDs=PMS_TaskAllocation.TaskIDs
BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A 34 6
2A2BE50E-301C-489A-8E91-60158EB81269 35 6
62AC266E-6567-44E8-8FD6-D30C1C5B2708 32,33,40 6
09C88927-24B6-4AC8-BA97-2C175EB92558 36 6
B9F70F43-3CEC-4FDD-837F-BF33B7935AF4 41 6
8084E143-F249-4BBC-8698-CBE67AE420D5 31 6
BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A 76 14
2A2BE50E-301C-489A-8E91-60158EB81269 77 14
62AC266E-6567-44E8-8FD6-D30C1C5B2708 74,75,81 14
09C88927-24B6-4AC8-BA97-2C175EB92558 78 14
B9F70F43-3CEC-4FDD-837F-BF33B7935AF4 72 14
8084E143-F249-4BBC-8698-CBE67AE420D5 71 14
这是更新后的结果,你看对不对!