AZGroups.com

Just a quick note, because I found it was hard to find this on the net...  Here's to hoping for a nice search index of this post.

It can be a pain to update a database table with Aggregate results, without resorting in either using an open result set and/or variable use.  I hope this can be helpful to others.

Option 1: Subquery
UPDATE   MyTable
SET      m_total = (
             SELECT  total(ot.col)
             FROM    OtherTable ot
             WHERE   ot.m_guid = MyTable.m_guid
         )

Option 2: Anonymous Join
UPDATE   MyTable
SET      m_total = IsNull(#tmp.[total], 0)
FROM     MyTable
JOIN     (
             SELECT   ot.m_guid,
                      total(ot.col) [total]
             FROM     OtherTable ot
             GROUP BY ot.m_guid
         ) #tmp
ON       #tmp.m_guid = MyTable.m_guid


Option 1 may work best when you only need a single piece of data.

Option 2 can work best when you need multiple aggregate results.  The result from the query inside the anonymous join is available through the temporary name assigned.  It's also a great use when you need to do more complex aggregate functionality.

Keywords: T-SQL, update, anonymous, join, subquery, aggregate

Just a note, below is the query I had needed to come up with..

UPDATE  #usertemp
SET     prereq_needed = ISNULL(#req.[needed], 0),
        prereq_done   = ISNULL(#req.[done], 1)
FROM    #usertemp
JOIN    (
            SELECT  cr.c_guid,
                    CONVERT(bit, count(c.c_id)) [needed],
                    CASE
                        WHEN count(c.c_id) = count(rd.r_instructor)
                            THEN 1
                        ELSE
                            0
                    END [done]
                FROM        cmi_course_req cr
                INNER JOIN  cmi_course c
                    ON      c.c_guid = cr.c_guid
                INNER JOIN  cmi_course c2
                    ON      c2.c_guid = cr.c_guid_req
                LEFT JOIN   cmi_lesson l
                    ON      l.c_guid = c2.c_guid
                LEFT JOIN   cmi_registration_data rd
                    ON      rd.l_guid = l.l_guid
                    AND     rd.s_guid = @sguid
                    AND     (   rd.cmi_core_lesson_status = 'passed'
                                OR
                                rd.cmi_core_lesson_status = 'completed'
                            )
                GROUP BY    cr.c_guid
        ) #req
ON      #req.c_guid = #usertemp.c_guid