SQL Server 2012 creating an update looping over 500 existing columns

Question

I wish to update date columns with NULL values using an existing value plus 5 years, PK is IdentityCourseID.

This is what I am trying to achieve:

UPDATE IdentityCourses
SET Expiry = DATEADD(year, 5, IdentityCourses.DateAttained)
WHERE IdentityCourseID = (SELECT IdentityCourseID
                          FROM IdentityCourses 
                          INNER JOIN UnitIdentities ON  IdentityCourses.IdentityID = UnitIdentities.IdentityID
                          WHERE (IdentityCourses.CourseID = 1041) 
                            AND (UnitIdentities.IsActiveMember = 1) 
                            AND (UnitIdentities.EndDate IS NULL) 
                            AND (IdentityCourses.Expiry IS NULL) )

Sounds like you need to use the "Update From" Syntax. See this question for an example: http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — momar, Apr 13 '16 at 12:17

score 0 · Answer 1 · answered Apr 13 '16 at 15:39

I think this will work for you:

UPDATE ic
SET Expiry = DATEADD(year, 5, ic.DateAttained)
FROM IdentityCourses AS ic
INNER JOIN UnitIdentities AS ui
  ON ic.IdentityID = ui.IdentityID
WHERE ic.CourseID = 1041
  AND ui.IsActiveMember = 1
  AND ui.EndDate IS NULL
  AND ic.Expiry IS NULL

SQL Server 2012 creating an update looping over 500 existing columns

1 Answers1