I have been doing some testing on removing the SELECT keyword from this update:

Update  #tmpDriverTime
Set     WorkDay2 = (Select IsNull(Sum(PyblHrs),0)
From    spr_DriverTimeEntry
Where   spr_DriverTimeEntry.DrvrID = #tmpDriverTime.EmployeeNo
And     Convert(Varchar(12),dtwrkd,112) = Convert(Varchar(12),@StartDate_1,112))

From the testing I have done it looks like the same data is returned whether or not I have the SELECT keyword. I ran the code by our DBA to get his approval of removing the SELECT statement and he said I can not as removing the SELECT statement would cause the update to work differently but he can not articulate how it works differently. I have done some searching on the new and found a few MSDN pages where they run the UPDATE FROM command with out a SELECT keyword. So now I am confused, my testing and other examples I found on MSDN say I can remove the SELECT keyword but my DBA says no. Does the removal of the SELECT keyword affect how the UPDATE FROM works and if so how does it affect it?

  • You're missing a parenthesis in your code. That missing parenthesis, depending on where it should be, could mean two very different answers. Jul 16, 2013 at 20:39
  • Missed the last paren in the copy/past. Added it in.
    – user11512
    Jul 16, 2013 at 20:40
  • 2
    If all you remove is the SELECT keyword, that should fail with a syntax error. Can you post both statements you are running so we can compare them ourselves? Also, can you give us a hint about what tables contain the columns PyblHrs and dtwrkd? We tend to use table aliases so that nobody has to have intimate knowledge of the schema to understand which table a column comes from... Jul 16, 2013 at 21:21
  • 2
    This is not an UPDATE...FROM statement. The FROM is a part of the (correlated) subquery, the SELECT. That is, your UPDATE statement has this form: UPDATE table SET column = (SELECT ... FROM ...), not this one: UPDATE table SET column = (SELECT ...) FROM .... In the latter case, the SELECT could indeed be omitted if it was just (SELECT scalar_expression). (Unless the expression included aggregate functions, but in case of (SELECT scalar_expression) using those wouldn't make much sense = unlikely.)
    – Andriy M
    Jul 17, 2013 at 8:52

1 Answer 1


Personally, I would re-write this in the following way, to clearly separate the source and the target, instead of using this subquery assignment technique. I am making assumptions on where some of these columns are coming from, of course, because you didn't use aliases, so the references were ambiguous.

SET @sd = DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate_1), 0);

;WITH hrs(DrvrID, ph) AS 
  SELECT EmployeeNo, SUM(PyblHrs)
    FROM #tmpDriverTime
    GROUP BY EmployeeNo
  -- "WorkDay2" represents a sum of payable hours? Quaint.
  SET WorkDay2 = COALESCE(hrs.ph, 0)
  FROM hrs
  INNER JOIN dbo.spr_DriverTimeEntry AS e -- schema prefix always, please
  ON hrs.DrvrID = e.DrvrID
  -- please don't convert both sides to a string
  -- use a range, then you can still use an index:
  WHERE e.dtwrkd >= @sd
    AND e.dtwrkd <  DATEADD(DAY, 1, @sd);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.