SQL关联表更新注意事项

javagtcpp

于 2013-04-02 16:48:17 发布

阅读量1k

点赞数

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.youkuaiyun.com/javagtcpp/article/details/8752238

版权

SQL 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了如何使用SQL进行关联表更新操作，通过一个派生表与目标表关联，实现数据同步。示例中展示了如何从多个表中获取数据，并更新到目标表jd_DeptPlanTaskExecute的相关字段，特别提醒在编写SQL更新语句时，需要明确指出表名而非使用别名。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

你可以将select 结果当成一个派生表与A表关联更新就可以了
类似如下
update A set 列名 = B.列名 from (select 编码,count(*)as 列名1,SUM(CASE WHEN 条件 THEN 1 ELSE 0 END) AS 列名 from c表 group by 编码) as B where A.编号 = B.编号

注意：上面的A表不能取别名，必须写的是表明.字段名

例子：

UPDATE jd_DeptPlanTaskExecute
SET TaskName = B.TaskName,TaskType = B.TaskType,DutyPeople = B.DutyPeople,Start = B.Start,Finish = B.Finish,Duration = B.Duration
FROM (
	SELECT z.ID AS ID, y.TaskName AS TaskName, y.TaskType AS TaskType, x.UserID AS DutyPeople,y.Start AS Start, y.Finish AS Finish, y.Duration AS Duration FROM jd_DeptPlanTaskExecute z LEFT JOIN jd_ProjectPlanTaskExecute y ON z.RefID = y.ID LEFT JOIN jd_ProjectPTRelatedUserExecute x ON x.UserType = 1 AND x.TaskID = y.ID WHERE z.PlanID = NEWID() AND z.IsTargetTask = 1 AND z.TaskOriginType = 1 AND NOT (z.DutyPeople<>x.UserID AND z.DutyDept<>x.OwnerDeptID) AND y.IsEnd = 1
) AS B
WHERE jd_DeptPlanTaskExecute.ID = B.ID