Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You're missing a parenthesis in your code. That missing parenthesis, depending on where it should be, could mean two very different answers. –  Thomas Stringer Jul 16 '13 at 20:39
    
Missed the last paren in the copy/past. Added it in. –  user11512 Jul 16 '13 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... –  Aaron Bertrand Jul 16 '13 at 21:21
1  
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 '13 at 8:52
add comment

1 Answer

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.

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

;WITH hrs(DrvrID, ph) AS 
(
  SELECT EmployeeNo, SUM(PyblHrs)
    FROM #tmpDriverTime
    GROUP BY EmployeeNo
)
UPDATE e
  -- "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);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.