lundi 27 avril 2020

UPDATE column if column is greater than a value

I have a table with these columns

  • proxy_id (primary key)
  • proxy (IP of a proxy)
  • current_requests (number of current requests the proxy has handled since its last_cool_down)
  • total_requests (number of requests this proxy has fulfilled in its lifetime)
  • last_used (datetime of last time this proxy was used)
  • last_cool_down (datetime of last time this proxy was on cooldown)

After I have used a proxy I run this command (obviously with the current date and time, but we will assume the current datetime is 2020-04-28 13:10:03)

UPDATE proxy_table
SET
current_requests = current_requests + 1,
total_requests = total_requests + 1,
last_used = '2020-04-28 13:10:03',
last_cool_down = '2020-04-28 13:10:03'
WHERE proxy_id = 1

My issue is I must ALWAYS update current_requests and last_used no matter what. However, in the above code, it doesn't consider this condition. If current_requests + 1 == 20, then current_requests = 0 and last_cool_down = '2020-04-28 13:10:03'. How can I do this?

So essentially something like this

UPDATE proxy_table

if (current_requests + 1 == 20) {
    SET current_requests = 0;
    SET last_cool_down = '2020-04-28 13:10:03';
else {
    SET current_requests = current_requests + 1;
}

SET total_requests = total_requests + 1;
SET last_used = '2020-04-28 13:10:03';
WHERE proxy_id = 1;

I want to do this in one sql statement, rather than running 2 statements. My attempt at this didn't work:

UPDATE proxy_table
SET
    CASE
        WHEN current_requests + 1 = 20 THEN current_requests = 0, last_cool_down = '2020-04-28 13:10:03' ELSE current_requests = current_requests + 1
    total_requests = total_requests + 1,
    last_used = '2020-04-28 13:10:03'
WHERE
proxy_id = 1

Aucun commentaire:

Enregistrer un commentaire