Update using MIN

I have two tables.

TableA

    Userid  Starttime               reason
    John    yyyy-mm-dd hh:mm:ss     logged in

Table B

    Userid  Date            Starttime              reason
    John    yyyy-mm-dd    yyyy-mm-dd hh:mm:ss     logged in

I need to update the starttime of table B with min(a.starttime) while a.Userid = b.userid, a.starttime = b开发者_JS百科.date, and reason = 'logged in'.

I seem to be having issues with using Min(a.starttime) as what I want to use to update.

My query is below:

update B
set B.starttime = (
  select Min(A.Starttime)
  from table as A
  where B.UserID = A.UserID
  and (CONVERT(DATETIME,A.DATE,102)) = (CONVERT(DATETIME,B.Date,102)))
  and  (A.Reason = 'loggedin')
)
from table2 as B

I convert the dates since table B has the date as for example 2011-09-13 00:00:00 and A has the date and times.

If you're just not getting the right results, it may be because you need to use convert(varchar, date, 102) instead of convert(datetime, date, 102). If that doesn't help, try this.

Instead of doing the subquery inside the SET portion, use it as a derived table and JOIN to it. Even if the above fixes the results, the below query should be far more efficient.

update B
set B.starttime = A.starttime
from table2 as B
INNER JOIN (
select A.UserId, convert(varchar, A.Date, 102) as adate, Min(A.Starttime) as starttime
  from table as A
  WHERE  (A.Reason = 'loggedin')
  GROUP BY A.UserId, convert(varchar, A.Date, 102)
) A on B.Nordia_ID = A.UserId and A.adate = convert(varchar, B.StatusDateTime, 102)

If you need to strip the time off, don't use CONVERT at all. Do this instead:

DATEADD(dd,0,DATEDIFF(dd,0,A.DATE)) = DATEADD(dd,0,DATEDIFF(dd,0,B.StatusDateTime))

They're dates, treat them like dates and you won't run into oddball errors from converting them to strings.