Get least used id within another table I have two tables

Account table

id INT, username TEXT, password TEXT, proxy_id INT, enabled BOOLEAN


Proxy table

id INT, proxy_ip TEXT, proxy_port INT


I would have a loop that goes through accounts one at a time. I want the least used proxy to be assigned to table.proxy_id.

for example if we have 2 proxies in the proxy table and 5 accounts in the Account table

1 10.0.0.1  4000
2 10.0.0.1  4001


Our accounts

1 david    password 2    enabled
2 mark     password 1    enabled
3 jessica  password 1    enabled
4 ashley   password NULL enabled
5 allan    password NULL enabled
6 james    password 2    disabled


My program will loop through all the enabled accounts, it will assign the least used proxy in the total enabled accounts to the account. In the example above David, mark and Jessica already have a proxy set. So the loop will go through Ashley and proxy with id 2 needs to be assigned to Ashley as it is the least used. For Allan proxy 1 or 2 can be assigned since it would be the least used in any case. James should be ignored since his account is not enabled.

I hope I was clear in my question. I think this would need to be done in two queries?

How would these queries look like?
NOTE : This solution is in SQL SERVER

You can do this using while loop.

Here is the query that will do

DECLARE @cnt INT = 1

WHILE @cnt &gt; 0
BEGIN
   update TOP (1) account
  set account.proxy_id = (
      select top 1 proxy.id
      from proxy order by (
        select count(account.id)
        from account
        where account.proxy_id = proxy.id and account.enabled = 1
      )
  ) where account.proxy_id is null
   SET @cnt = (select count(id) from account where proxy_id is null and enabled = 1)
END;


In this query first we count all account for proxy.id in proxy table and select the proxy.id for lowest count.

Then we set that id to user who is enabled and does not have any proxy_id

UPDATE :

In POSTGRESQL you can achieve this by

DO $$DECLARE r int;
BEGIN
r=1;
    while r &gt; 0 
    LOOP
        update account set proxy_id = (
            select proxy.id from proxy order by (
                select count(account.id)
                from account
                where account.proxy_id = proxy.id and account.enabled = '1'
            ) limit 1
        ) where id = (
                select id
                from account
                where proxy_id is null and enabled ='1'
                order by id limit 1
        );
        r = (select count(id) from account where proxy_id is null and enabled ='1');
    END LOOP;
END$$;