I have a very simple table where I keep player bans. There are only two columns (not actually, but for simplicity's sake) - a player's unique ID (uid) and the ban expire date (expiredate)
I don't want to keep expired bans in the table, so all bans where expiredate < currentdate need to be deleted.
To see if a player is banned, I query this table with his uid and the current date to see if there are any entries. If there are - we determine that the player is banned.
So I need to run two queries. One that would fetch me the bans, and another that would clean up the table of redundant bans.
I was wondering if it would be possible to combine these queries into one. Select and return the entry if it is still relevant, and remove the entry and return nothing if it is not.
Are there any nice ways to do this in a single select query?
Edit:
To clarify, I actually have some other information in the table, such as the ban reason, the ban date, etc. so I need to return the row as well as delete irrelevant entries.
Unfortunately you cannot have a delete statement inside a select statement... I got you a link from sqlite docos for your reference.
Select statements are only used for retrieving data. Although a select could be used in a sub-query for a delete statement, it would still be used for retrieving data.
You must execute the two statements in separated in your case.