AlliedModders - SQL - Updating ID's efficiently

AlliedModders (https://forums.alliedmods.net/index.php)

- Scripting (https://forums.alliedmods.net/forumdisplay.php?f=107)

- - SQL - Updating ID's efficiently (https://forums.alliedmods.net/showthread.php?t=333980)

SQL - Updating ID's efficiently

Hello,
i have a table where i want to fill in numbers for the ID starting from 1 then 2 and so on.
The table is ordered by time so the most recent time should get number 1.

There is a query which works on phpmyadmin but not directly in sourcemod:
SET @i:=0; UPDATE mytable SET ID = @i:=(@i+1) ORDER BY time DESC;

The table before

HTML Code:

mytable

ID                  time

null                1629156967

null                1628735917

null                1628513592

How the result should be:

HTML Code:

mytable

ID                  time

1                   1629156967

2                   1628735917

3                   1628513592

Is there a good way to do this? I dont really wanna loop over every row to achieve this.

Re: SQL - Updating ID's efficiently

Can you elaborate on what exactly this is for, and what your final goal will be?

Quote:

Originally Posted by xice1337 (Post 2755810)

The table is ordered by time so the most recent time should get number 1.

This one sounds like you want it dynamically, - similar to a ranking system?

If I'm the newest (highest number) right now, according to the Unix timestamp / EPOCH field "time", then I should be #1?

If then you're the newest in five minutes, then you need to be first, and I need to be #2 ( ... or even a later number )?

Quote:

Originally Posted by xice1337 (Post 2755810)

Is there a good way to do this? I dont really wanna loop over every row to achieve this.

If you really insist on getting you database updated accordingly, in the "dynamic" style as explained above, you will need to "loop over every row" every time it needs an update.

However, if you're solely looking for the current rank (position) of the actual result, at the time of a SELECT query, you can use e.g.:

Code:

SET @position=0; SELECT (@position:=@position+1) AS _position, time FROM mytable ORDER BY time DESC;

to accomplish what you want.

Re: SQL - Updating ID's efficiently

Quote:

Originally Posted by DarkDeviL (Post 2755831)

This one sounds like you want it dynamically, - similar to a ranking system?

That is correct.
My workaround for that is now that i made a http request via SteamWorks to my apache server which handles this task.

Re: SQL - Updating ID's efficiently

You can create your ID column in the db as autoincrement.
Then when you do your insert you just insert the time column and the DB automaticially handles the ID column.

For SQLite see this.
https://www.tutorialspoint.com/sqlit...oincrement.htm