SQL 更新有关问题-MSSQL教程-爱易网页

SQL 更新有关问题

日期：2014-05-18　浏览次数：20571 次

SQL 更新问题

我想要的结果就是：将表【PMS_TaskAllocation】里面ProjectID=14的TaskIDs替换成新的ID，也就是

SQL code

GUID                                        TaskIDs              ProjectID
------------------------------------------- -------------------- -----------
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


以下是数据
--------------------------------------
select GUID,TaskIDs,ProjectID
from dbo.PMS_TaskAllocation
where ProjectID in (6,14)
--------------------------------------
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        34                   14
2A2BE50E-301C-489A-8E91-60158EB81269        35                   14
62AC266E-6567-44E8-8FD6-D30C1C5B2708        32,33,40             14
09C88927-24B6-4AC8-BA97-2C175EB92558        36                   14
B9F70F43-3CEC-4FDD-837F-BF33B7935AF4        41                   14
8084E143-F249-4BBC-8698-CBE67AE420D5        31                   14



select ID,GUID,ProjectID
from dbo.PMS_Task
where ProjectID in (6,14)
--------------------------------------
ID          GUID                                 ProjectID
----------- ------------------------------------ -----------
30          6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1 6
31          4EDE656D-E05B-4745-B816-2E6DF042FD7B 6
32          1264B5B3-9D48-4CB7-BF15-57B81A9EB79D 6
33          D2A7B904-F733-42F9-B693-31ABFC5F6DF1 6
34          C3E2B888-1DCB-49C3-B1C4-5D96195CD057 6
35          E34618A2-C2A8-483B-AD17-FC32BEFD8D87 6
36          B2A3CEB7-CEC1-4A69-9150-A2FE30513211 6
37          6DF78928-00F1-4725-92E2-E9665C11CEA7 6
38          B9913A50-637B-473D-91A0-FEBBA3E89E44 6
40          591F409D-7F63-4494-8D18-40DEF8AF7C99 6
41          78A2409A-3E6B-414F-85B2-DAEAFFE5E913 6
42          23041727-869D-4B6D-8A62-8F8128FB70D1 6
70          6D6A88CC-862F-4AEA-BB84-0CF13FA6AAA1 14
71          4EDE656D-E05B-4745-B816-2E6DF042FD7B 14
72          78A2409A-3E6B-414F-85B2-DAEAFFE5E913 14
73          23041727-869D-4B6D-8A62-8F8128FB70D1 14
74          1264B5B3-9D48-4CB7-BF15-57B81A9EB79D 14
75          D2A7B904-F733-42F9-B693-31ABFC5F6DF1 14
76          C3E2B888-1DCB-49C3-B1C4-5D96195CD057 14
77          E34618A2-C2A8-483B-AD17-FC32BEFD8D87 14
78          B2A3CEB7-CEC1-4A69-9150-A2FE30513211 14
79          6DF78928-00F1-4725-92E2-E9665C11CEA7 14
80          B9913A50-637B-473D-91A0-FEBBA3E89E44 14
81          591F409D-7F63-4494-8D18-40DEF8AF7C99 14

------解决方案--------------------

SQL code


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=6

with 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 更新有关问题

相关资料更多>

推荐阅读更多>