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 an existing UPDATE table SET statement that was working until one of the Drivers used two different trucks in his/her shift, so now what I need to do is modify the UPDATE statement so I can use the MIN() function on a few columns. When I tried to add the MIN() function to the statement as it exists I received the error

An aggregate may not appear in the set list of an UPDATE statement."

Here is my existing code for the UPDATE statement:

UPDATE #tmpDriverTime
SET @StartPlus8 =   CASE 
                        WHEN  ((CONVERT(int, DTE.StrtTm) + 800) > 2400) AND (CONVERT(INT, DTE.StartBreak) < 1600) THEN ((CONVERT(INT, DTE.StrtTm) + 800) - 2400)
                        ELSE (CONVERT(INT, DTE.StrtTm) + 800)
                    END,
    WorkDay1Break = CASE
                        WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
                        WHEN ((WorkDay1 > 8 AND DTE.StartBreak IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
                        WHEN (@StartPlus8 < CONVERT(INT, DTE.StartBreak) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
                        ELSE 0
                    END
FROM dbo.spr_DriverTimeEntry AS DTE
WHERE DTE.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), DTE.dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND DTE.PyrllID IN (
    1,
    222,
    1013,
    1014
);

Example inputs:

 Dtwrkd  Driver  StrTm  StartBreak
 1/1/11    1     0430   NULL 
 1/1/11    1     1200   1430

Goal results:

 Dtwrkd  Driver  StrTm  StartBreak
 1/1/11    1     0430   1430

I am not able to change the table schema so please don't come in here tell me I should redesign the entire then so it is setup properly, I don't have that luxury.

Code I tried that produced the error:

UPDATE #tmpDriverTime
SET @StartPlus8 =   CASE 
                        WHEN  ((CONVERT(int, MIN(DTE.StrtTm)) + 800) > 2400) AND (CONVERT(INT, DTE.MIN(StartBreak)) < 1600) THEN ((CONVERT(INT, MIN(DTE.StrtTm)) + 800) - 2400)
                        ELSE (CONVERT(INT, MIN(DTE.StrtTm)) + 800)
                    END,
    WorkDay1Break = CASE
                        WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
                        WHEN ((WorkDay1 > 8 AND MIN(DTE.StartBreak) IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
                        WHEN (@StartPlus8 < CONVERT(INT, MIN(DTE.StartBreak)) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
                        ELSE 0
                    END
FROM dbo.spr_DriverTimeEntry AS DTE
WHERE DTE.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), DTE.dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND DTE.PyrllID IN (
    1,
    222,
    1013,
    1014
);
share|improve this question
    
So, do you want to update both rows? Or there is only one row per driver in the #tmpDriverTime table? Or one row per driver per day? –  ypercube Jul 30 '13 at 12:43
    
Another thing is that the various columns used in the CASE and the WHERE are not clear which of the two tables belong to. Please alias all columns. –  ypercube Jul 30 '13 at 12:46
    
Can you add the sql you tried, which gave you the error above so we can get a clear idea of what you're trying to achieve. –  Tanner Jul 30 '13 at 13:09
    
#tmpDrivertime needs to only contain one row per driver per day, no matter how many trucks driven. I aliased the DrivertimeEntry as DTE the other columns belong to the temp table. –  user11512 Jul 30 '13 at 13:22
    
With the query you have, it seems like there is one row per driver (in the temp table), not per day. If there are many rows per driver, they will all be updated, with the same results, from the one @StartDate. –  ypercube Jul 30 '13 at 14:05
show 1 more comment

1 Answer 1

I'm not going to try to understand or re-write your entire query, but the typical form to get around this parser limitation is to calculate the aggregates in a CTE. The basic idea:

;WITH cte(id, dt) AS 
(
  -- simplifying here, you'll need to with your logic
  SELECT DrvrID, MIN(datetimecolumn) 
  FROM dbo.spr_DriverTimeEntry
  -- WHERE ...
  GROUP BY DrvrID
)
UPDATE t
  SET t.StartPlus8 = cte.dt
  FROM #tmpDriverTime AS t
  INNER JOIN cte
  ON cte.id = t.EmployeeNo;

Why create the #temp table with a different column name (EmployeeNo) instead of what it really represents (DrvrID)?

Are you updating a #table or setting a variable? Pick one.

Also try to avoid converting a date to a string to see if they're on the same day - this is by far one of the least efficient ways to do so.

share|improve this answer
    
The DB is part of a software set from a third party so I have no control over the naming conventions. I am both UPDATING the table AND setting the Variable. I left the conversions in for the dates because as soon as I dropped them the whole script stopped working right. I am not concerned with cleaning up this code I am only concerned with getting the MIN() value to function in it. I have never head of CTE can you explain that more –  user11512 Jul 30 '13 at 17:04
    
I don't see why you say I cant set a variable from an update command as I clearly am and it is working just as expected with the code I have. –  user11512 Jul 30 '13 at 17:27
    
The update only affects the single row in tempDrivertime that matches the where conditions. the StartPlus8 gets assigned the correct data depending on the trueness of the when statement. SetPlus8 is not in the temp table but WorkDay1Break is and that gets updated like it should. –  user11512 Jul 30 '13 at 17:33
    
Ok, well, as long as you're clear that the UPDATE can only ever affect one row. So if you need to update 10 rows, do you use a loop? What do you do with the variable value? –  Aaron Bertrand Jul 30 '13 at 17:36
add comment

Your Answer

 
discard

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