1. 爱易网页
  2. MSSQL教程
  3. SQL 更新有关问题

SQL 更新有关问题

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

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

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

相关资料更多>

  1. 求产品跟配件关系表设计选择的取舍和查询语句
  2. 怎么取0点开始至今的时间段集合, 间隔1小时
  3. 这个分类计算的有关问题怎么写
  4. mssql 删除表时出错
  5. 求一条SQL语句，要求把A表某个字段的值，赋成这A表的ID=31的这条记录的这个字段的值解决方法
  6. 请教怎么修改bit类型的值
  7. 怎么统计每一列中各项的个数
  8. 多个表统计结果合成一个表解决方法
  9. 聚合不应出现在 UPDATE 语句的集合列表中 的有关问题求解