I have two tables
id INT, username TEXT, password TEXT, proxy_id INT, enabled BOOLEAN
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
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 in Java that goes through all the enabled accounts, it will assign the least used proxy in the 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?