Thank you all.
Although, it will get dropped for good once any of its input is spent by another transaction.
I can't get my head around why the transaction would get "dropped for good once any of its input is spent by another transaction."
The transaction fee is low and that was why the server that your wallet is connected to dropped it, but it is still in other mempool. If you increase the fee or bump the fee to a higher one, the transaction will be added back to the mempool, and because miners are selecting transactions with high fee, if they add your transaction to the next block and it gets confirmed, your old transaction will be dropped because the first transaction that will be successfully added to the blockchain is what will be valid, and the other one will be invalid because they both have the same input.
What you can do is remove the transaction from the sending wallet, which will allow you to spend those coins in a new transaction. If the transaction is showing as local, simply right click on it and delete it. This will indeed reset your balances, and you can then make a new transaction sending the same coins (plus any other coins you want to send) with a higher fee.