Better way of conditionally updating many rows

[10409]

Hi,

I've got a quite big Table of User-Sessions with basically an ID and an END_TIME, for when the Session ends (a date).

Now, outside the Database, I've got an also quite big list of Session-IDs, that have to be kept alive, by updating the END_TIME. This list is in Java.

How would I do this in a nice and performant manner?

Would I use something like:
UPDATE active_sessions SET end_time=getdate() WHERE ( id=1 or id=2 or id=3 or id=4 ........... )

or submit a query for each single one, like:
UPDATE active_sessions SET end_time=getdate() WHERE (id=1)
UPDATE active_sessions SET end_time=getdate() WHERE (id=2)
........

Is there another way I can do that?
Because both options don't seem elegant to me.
Mind you, there are up to a couple of hundred IDs to update!

I would be grateful for any help.

 

[johpje]

dear 10409,

In your example it seems as you want to update every record with that field. in that case you should use no WHERE clause:
UPDATE active_sessions SET end_time=getdate()

In case you want to update only a part of the rows, there are a 2 possibilities:

preferably you should use a sub-query:
UPDATE active_sessions SET end_time=getdate()
WHERE id IN (SELECT id FROM Any_Table WHERE ...)

Note: the specified query should only have one field selected like the example. IN (SELECT id, user FROM...) is not possible.

If there is no way you can select the ids within a SELECT you can also use the IN keyword to use a list of the values you want.
UPDATE active_sessions SET end_time=getdate() WHERE id IN (1, 2, 3, 4, 5, ....)
This however, should always be avoided if possible.

Regards,
johpje

 

[10409]

Thanks johpje,

unfortunately only the last case applies to my problem. At least the IN keyword makes it look a bit nicer.
But I suppose i'll need to rethink the whole process and do something more clever. Introducing another table might be a good idea actually.

Another option might be setting the END_TIME 10 minutes back for each user that falls out of the external list, and then updating like this:

UPDATE active_sessions SET end_time=getdate()+6minutes WHERE (end_time>getdate())

every 5 minutes.
Or is date comparison too slow?

thanks again you for your help!

Regards,
10409.