修改数据,需要验证后修改对应的数据,而验证的字段不再修改表中,就需要对多表进行查询,花了些时间想出了如下办法,感觉还是挺有用的,也挺过瘾的,之前很少碰到这样的,所以再次记录下:
(此业务逻辑没有详细说明,如果你正在看这些,只需要看SQL的写的语法,相信您能看懂,也希望对您有所帮助)
- -----------------------------------------
- --Update 与 Select 一起的用法
- --Update 通过多表来修改数据
- -----------------------------------------
- --参考写法1:
- update B1 set strNum = strNum - B2.intNum
- where strId in
- (select intId from B2 where B2.intId = B1.strId)
- --参考写法2:
- update B1 set strNum = strNum - B2.intNum
- from B1 inner join B2
- on B2.intId = B1.strId
- select *
- from pro_res_assign,pro_gantt_task
- where pro_res_assign.task_id = pro_gantt_task.task_id
- and pro_res_assign.resource_id = 'jxstar4918'
- update pro_gantt_task
- set pro_gantt_task.responsible_id = pro_res_assign.user_id ,
- pro_gantt_task.responsible = pro_res_assign.res_name
- from pro_res_assign,pro_gantt_task
- where pro_res_assign.task_id = pro_gantt_task.task_id
- and pro_res_assign.resource_id = 'jxstar4918'
- ------------------------------
- --最终完成的SQL
- ------------------------------
- update pro_gantt_task
- set pro_gantt_task.responsible_id = (
- case when pro_res_assign.responsible_type = '1' then pro_res_assign.user_id else null end
- ),
- pro_gantt_task.responsible = (
- case when pro_res_assign.responsible_type = '1' then pro_res_assign.res_name else null end
- )
- from pro_res_assign,pro_gantt_task
- where pro_res_assign.task_id = pro_gantt_task.task_id
- and pro_res_assign.resource_id = 'jxstar4918'
- --测试查询
- select res_type,user_id,res_name,task_id from pro_res_assign where resource_id = 'jxstar4918'
- select responsible_id,responsible from pro_gantt_task
- 以下是WBS计划反写备份:
- --select user_id,res_name,task_id from pro_res_assign where resource_id = ?
- --update pro_gantt_task set responsible_id = ? ,responsible = ? where task_id = ?
这里只对SQL Server2008 测试通过,不知道Oracle/Mysql 其他的数据库还没测试;