Update a specific row with data from another table

Question

I am using MS SQL. I want to update a specific row within a table with data from another table. I have created a query that will get the specific row I want to update. Please note that I have used a select query to select the specific row that needs to be updated. Also note that there is a sub query used to get the right row. For me, this makes it difficult to incorporate into a set statement.

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

This gives me

Now I want to update the overview column with data from another table. This select query gives me the info I want to use to update the other table.

SELECT AssessmentCode, Overview
FROM SubjectAssessments
WHERE (ClassCampus = 'S') 
    and (FileYear = 2015)   
    and (FileSemester = 3) 
    and filetype = 'A' 
    and AssessmentCode like '08%'

This gives me

Can someone please help me with the syntax to update the overview column from the row obtained in the first query above with the overview column contained in the second query where the Query1.AssessmentCode = Query2.AssessmentCode from both queries.

How can I use a set statement but then use the first query above to say which row to set? Other similar questions just use a simple set and then a field without any where statements.

Possible duplicate of How to update data in one table from corresponding data in another table in SQL Server 2005 — Gilad Green
– Gilad Green, Commented Jul 15, 2016 at 10:21
Yes, I did look at this but i am not sure how to incorporate the first select query to get the correct row to set. — David Phillips
– David Phillips, Commented Jul 15, 2016 at 10:28

dean · Accepted Answer · 2016-07-15 11:18:32Z

1

Just join these two in an updateable 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

answered Jul 15, 2016 at 11:18

dean

10.1k2 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Phillips Over a year ago

Thanks so much. This works well. Have not used CTEs much and this is a nice simple solution.

Siyavash Hamdi · Accepted Answer · 2016-07-15 11:04:55Z

Try following:

;WITH cteBaseInfo AS
(
    SELECT AssessmentCode, Overview
    FROM SubjectAssessments
    WHERE (ClassCampus = 'S') 
        and (FileYear = 2015)   
        and (FileSemester = 3) 
        and filetype = 'A' 
        and AssessmentCode like '08%'
)

, cteToBeUpdated 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.
)

UPDATE  subjectassessmentareas
SET     Overview = (SELECT Overview FROM cteBaseInfo WHERE AssessmentCode = subjectassessmentareas.AssessmentCode)
WHERE   AssessmentCode IN (SELECT AssessmentCode FROM cteToBeUpdated)

Please note that, here AssessmentCode should be Primary Key.

Collectives™ on Stack Overflow

Update a specific row with data from another table

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related