If a membership is auto-renew than it should be linked to recurring contribution(See under contribution tab >> recurring tab).
To turn off auto-renew for a membership, you can either
Cancel the recurring, that should cancel ongoing recurring payment and also membership renewal
Run Below query to only turn off membership renewal
Sorry for the confusion here folks, I was mixing 'profiles' with 'custom field sets'.
My membership profile contained custom membership fields and this profile wasn't available when editing or creating some specific membership types.
However, what I didn't realise was that when setting up custom membership fields you must choose which memberships you want ...