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

SQL 更新有关问题

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

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. 为什么小弟我不能安装SQL Server 2005
  2. sql server 运算,该怎么解决
  3. 分栏显示并排序且加小计与合计,请各位帮忙解决方案
  4. 装了sql server 2005 express,开始菜单里只有个服务配置工具的菜单,没有企业管理器的菜单,到具体的装sql server的文件夹里找到sqlserver.解决方法
  5. 请问一个sel server 语句
  6. sql server2008 开释内存
  7. 游标定义为datetime类型出现的有关问题
  8. 简单有关问题。多个类别名称的查询
  9. 用友数据库有关问题