我正在使用MS SQL。我想用另一个表中的数据更新表中的特定行。我创建了一个查询,该查询将获取要更新的特定行。请注意,我已经使用了选择查询来选择需要更新的特定行。还要注意,有一个子查询用于获取正确的行。对我而言,这使得很难将其纳入既定陈述中。
select tbl1.assessmentcode, tbl1.Overview from subjectassessmentareas tbl1
inner join
(
select assessmentcode,MIN(areaseq) as minassessarea from subjectassessmentareas
where resultgroup = 'PR_Yr8_2' and ResultType = 'KUS_5'
group by AssessmentCode
) tbl2
on tbl1.AssessmentCode = tbl2.AssessmentCode and tbl1.AreaSeq = tbl2.minassessarea
where fileyear = 2016 and filesemester = 3
这给了我
现在,我想用另一个表中的数据更新概述列。该选择查询为我提供了我想用来更新其他表的信息。
SELECT AssessmentCode, Overview
FROM SubjectAssessments
WHERE (ClassCampus = 'S')
and (FileYear = 2015)
and (FileSemester = 3)
and filetype = 'A'
and AssessmentCode like '08%'
这给了我
有人可以用语法帮助我,以将上面第一个查询中获得的行中的概述列更新为第二个查询中包含的概述列,其中两个查询中的Query1.AssessmentCode = Query2.AssessmentCode。
如何使用set语句,然后使用上面的第一个查询说要设置哪一行?其他类似的问题仅使用一个简单的集合,然后使用没有任何where语句的字段。
只需将这两个加入可更新的CTE中即可:
;with x as (
select tbl1.assessmentcode, tbl1.Overview
from subjectassessmentareas tbl1
inner join
(
select assessmentcode,MIN(areaseq) as minassessarea from subjectassessmentareas
where resultgroup = 'PR_Yr8_2' and ResultType = 'KUS_5'
group by AssessmentCode
) tbl2
on tbl1.AssessmentCode = tbl2.AssessmentCode and tbl1.AreaSeq = tbl2.minassessarea
where fileyear = 2016 and filesemester = 3
),
y as (
SELECT AssessmentCode, Overview
FROM SubjectAssessments
WHERE (ClassCampus = 'S')
and (FileYear = 2015)
and (FileSemester = 3)
and filetype = 'A'
and AssessmentCode like '08%'
),
z as (
select x.Overview as dest, y.Overview as src
from x join y on x.AssessmentCode = y.AssessmentCode
)
update z set dest = src
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句