CODEWITHSUNDEEP
sqlsql-updatemax
CurtisHx
CurtisHx

Reputation: 758

Updating sql record based on 2 values in table

I have a table that has the following columns:

GRVLID
GRID
Timein
Timeout

This table is part of a visitor tracking system.

There is another table that holds each visitor's information. This table keeps track of each time the visitor logs in and out.

GRVLID is the unique record identifier. GRID is the visitor identifier number. Each visitor has their own GRID number. Timein is the datetime when the visitor logged in. Timeout is the datetime when the visitor logged out.

Every time a visitor logs in, a new record is created. So it would be possible to have multiple records with the same GRID number. The GRVLID number is unique and just increments as new records are added.

What I need is an update-set-where statement that updates the record that has the highest GRVLID number for a given GRID number. I tried doing:

update database
set [TimeOut] = @p_timeOut
where (GRID = @p_GRID and MAX(GRVLID))

but that doesn't seem to play nice with MSSQL. Any ideas?

Upvotes: 1

Views: 217

Answers (2)

aF.
aF.

Reputation: 66687

Possibly like this:

update database
set [TimeOut] = @p_timeOut
from database
where GRVLID in
(select max(GRVLID) from database group by GRID)

With select max(GRVLID) from database group by GRID you select the max value of GRVLID for each GRID.

Afterwords, you update the rows that have the GRVLID selected.

Upvotes: 0

ean5533
ean5533

Reputation: 8994

I think what you're looking for is more like this:

update database
set [TimeOut] = @p_timeOut
where GRID = @p_GRID 
and GRVLID = (select MAX(GRVLID) from database WHERE where GRID = @p_GRID)

There are more efficient ways to re-write this query, but if performance isn't a concern then this will work fine and it's easy to understand.

P.S. I hope your table really isn't named "database".

Upvotes: 1

Related Questions