SQL 更新问题
我想要的结果就是：将表【PMS_TaskAllocation】里面ProjectID=14的TaskIDs替换成新的ID，也就是
GUID TaskIDs ProjectID------------------------------------------- -------------------- -----------BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A 76 142A2BE50E-301C-489A-8E91-60158EB81269 77 1462AC266E-6567-44E8-8FD6-D30C1C5B2708 74,75,81 1409C88927-24B6-4AC8-BA97-2C175EB92558 78 14B9F70F43-3CEC-4FDD-837F-BF33B7935AF4 72 148084E143-F249-4BBC-8698-CBE67AE420D5 71 14以下是数据--------------------------------------select GUID,TaskIDs,ProjectIDfrom dbo.PMS_TaskAllocationwhere ProjectID in (6,14)--------------------------------------GUID TaskIDs ProjectID------------------------------------------- -------------------- -----------BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A 34 62A2BE50E-301C-489A-8E91-60158EB81269 35 662AC266E-6567-44E8-8FD6-D30C1C5B2708 32,33,40 609C88927-24B6-4AC8-BA97-2C175EB92558 36 6B9F70F43-3CEC-4FDD-837F-BF33B7935AF4 41 68084E143-F249-4BBC-8698-CBE67AE420D5 31 6BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A 34 142A2BE50E-301C-489A-8E91-60158EB81269 35 1462AC266E-6567-44E8-8FD6-D30C1C5B2708 32,33,40 1409C88927-24B6-4AC8-BA97-2C175EB92558 36 14B9F70F43-3CEC-4FDD-837F-BF33B7935AF4 41 148084E143-F249-4BBC-8698-CBE67AE420D5 31 14select ID,GUID,ProjectIDfrom dbo.PMS_Taskwhere ProjectID in (6,14)--------------------------------------ID GUID ProjectID----------- ------------------------------------ -----------30 6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1 631 4EDE656D-E05B-4745-B816-2E6DF042FD7B 632 1264B5B3-9D48-4CB7-BF15-57B81A9EB79D 633 D2A7B904-F733-42F9-B693-31ABFC5F6DF1 634 C3E2B888-1DCB-49C3-B1C4-5D96195CD057 635 E34618A2-C2A8-483B-AD17-FC32BEFD8D87 636 B2A3CEB7-CEC1-4A69-9150-A2FE30513211 637 6DF78928-00F1-4725-92E2-E9665C11CEA7 638 B9913A50-637B-473D-91A0-FEBBA3E89E44 640 591F409D-7F63-4494-8D18-40DEF8AF7C99 641 78A2409A-3E6B-414F-85B2-DAEAFFE5E913 642 23041727-869D-4B6D-8A62-8F8128FB70D1 670 6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1 1471 4EDE656D-E05B-4745-B816-2E6DF042FD7B 1472 78A2409A-3E6B-414F-85B2-DAEAFFE5E913 1473 23041727-869D-4B6D-8A62-8F8128FB70D1 1474 1264B5B3-9D48-4CB7-BF15-57B81A9EB79D 1475 D2A7B904-F733-42F9-B693-31ABFC5F6DF1 1476 C3E2B888-1DCB-49C3-B1C4-5D96195CD057 1477 E34618A2-C2A8-483B-AD17-FC32BEFD8D87 1478 B2A3CEB7-CEC1-4A69-9150-A2FE30513211 1479 6DF78928-00F1-4725-92E2-E9665C11CEA7 1480 B9913A50-637B-473D-91A0-FEBBA3E89E44 1481 591F409D-7F63-4494-8D18-40DEF8AF7C99 14
update PMS_TaskAllocationset [TaskIDs]=a.ID from PMS_Task a where a.[GUID]=PMS_TaskAllocation.[GUID] and PMS_TaskAllocation.ProjectID=14 and a.ProjectID=6with tas(select * from PMS_Task where [ProjectID]=14)update PMS_TaskAllocationset TaskIDs=t.ID from t where t.[GUID]=PMS_TaskAllocation.[GUID] and PMS_TaskAllocation.ProjectID=6
[解决办法]
if OBJECT_ID('PMS_TaskAllocation') is not null drop table PMS_TaskAllocationcreate table PMS_TaskAllocation(GUID uniqueidentifier,TaskIDs nvarchar(20),ProjectID int)insert into PMS_TaskAllocationselect 'BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A','34',6 union allselect '2A2BE50E-301C-489A-8E91-60158EB81269','35',6 union allselect '62AC266E-6567-44E8-8FD6-D30C1C5B2708','32,33,40',6 union allselect '09C88927-24B6-4AC8-BA97-2C175EB92558','36', 6 union allselect 'B9F70F43-3CEC-4FDD-837F-BF33B7935AF4','41',6 union allselect '8084E143-F249-4BBC-8698-CBE67AE420D5','31',6 union allselect 'BDF42CAF-2FED-40EB-9EC7-E30683DC7F1A','34',14 union allselect '2A2BE50E-301C-489A-8E91-60158EB81269','35',14 union allselect '62AC266E-6567-44E8-8FD6-D30C1C5B2708','32,33,40',14 union allselect '09C88927-24B6-4AC8-BA97-2C175EB92558','36',14 union allselect 'B9F70F43-3CEC-4FDD-837F-BF33B7935AF4','41',14 union allselect '8084E143-F249-4BBC-8698-CBE67AE420D5','31',14 if OBJECT_ID('PMS_Task') is not null drop table PMS_Taskcreate table PMS_Task(ID int,GUID uniqueidentifier,ProjectID int)insert into PMS_Taskselect 30,'6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1',6 union allselect 31,'4EDE656D-E05B-4745-B816-2E6DF042FD7B',6 union allselect 32,'1264B5B3-9D48-4CB7-BF15-57B81A9EB79D',6 union allselect 33,'D2A7B904-F733-42F9-B693-31ABFC5F6DF1',6 union allselect 34,'C3E2B888-1DCB-49C3-B1C4-5D96195CD057',6 union allselect 35,'E34618A2-C2A8-483B-AD17-FC32BEFD8D87',6 union allselect 36,'B2A3CEB7-CEC1-4A69-9150-A2FE30513211',6 union allselect 37,'6DF78928-00F1-4725-92E2-E9665C11CEA7',6 union allselect 38,'B9913A50-637B-473D-91A0-FEBBA3E89E44',6 union allselect 40,'591F409D-7F63-4494-8D18-40DEF8AF7C99',6 union allselect 41,'78A2409A-3E6B-414F-85B2-DAEAFFE5E913',6 union allselect 42,'23041727-869D-4B6D-8A62-8F8128FB70D1',6 union allselect 70,'6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1',14 union allselect 71,'4EDE656D-E05B-4745-B816-2E6DF042FD7B',14 union allselect 72,'78A2409A-3E6B-414F-85B2-DAEAFFE5E913',14 union allselect 73,'23041727-869D-4B6D-8A62-8F8128FB70D1',14 union allselect 74,'1264B5B3-9D48-4CB7-BF15-57B81A9EB79D',14 union allselect 75,'D2A7B904-F733-42F9-B693-31ABFC5F6DF1',14 union allselect 76,'C3E2B888-1DCB-49C3-B1C4-5D96195CD057',14 union allselect 77,'E34618A2-C2A8-483B-AD17-FC32BEFD8D87',14 union allselect 78,'B2A3CEB7-CEC1-4A69-9150-A2FE30513211',14 union allselect 79,'6DF78928-00F1-4725-92E2-E9665C11CEA7',14 union allselect 80,'B9913A50-637B-473D-91A0-FEBBA3E89E44',14 union allselect 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 ajoin PMS_Task b on CHARINDEX(convert(nvarchar(10),b.ID),a.TaskIDs,1)<>0where a.ProjectID=14),t2 as(select TaskIDs,b.ID from t1 ajoin 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 ) twhere ProjectID=14 and t.TaskIDs=PMS_TaskAllocation.TaskIDs