Continuing the SQL analogy from the OP, you absolutely should be able to UPDATE an entire table, but probably the syntax is at least somewhat to blame, because very rarely you want to run an update on every row. A simple change could be that an UPDATE without a WHERE clause is a syntax error (you could still add "WHERE TRUE", if that's what you mean to do).
Another example is how the React API uses funny method names like "dangerouslySetInnerHTML" for things you aren't usually supposed to do.
I'm a big believer in making invalid states unrepresentable, and a straightforward extension of that modus operandi could be "make unlikely states hard to reach".
If the number of pushes is small and the time to make the change is small, automating the change, but running it one at a time in a loop works ok. When things start breaking, you can stop the loop before too many servers fall over. If you have a lot of servers, you can split your hosts and run up to about 10 terminals doing loops before it gets really hard to supervise. Often, you can easily parallelize the prep part of the update, and leave only a quick change to be serialized.
I'll have to see if I can find it, but yinst-pw was opensourced somewhere and is really useful for sudo password prompts if you're doing it half-way like this. Edit: ahah, remembered it got renamed to autopw https://github.com/jschauma/sshscan/blob/master/src/autopw
With 20.000 servers, for example, an automated process could roll out updates to 1,000 every hour during 20 hours, check that response times, system load, etc. are within limits for the updated set during each hour, and send out alerts and pause updating when they do not.
So, the user still would press one button to do the update, but the change would slowly take effect, allowing both the system and humans to take action if needed.
Main problem there is to keep things flexible enough to allow somewhat out of the box updates. And of course, that requires that you can run with half your servers on a different version of your software.
you probably also will have to forget doing the entire update in a single transaction.
However, you still have things like "this update severs the machine from the management system due to unexpected XYZ", "this update is fine until it's rolled out to 80% of the world, at which point interactions with the other deployed systems hammer the system so hard the management interface can't get in properly", "this update looked fine because it was using almost entirely cached data but once the caches all expired it turned out to be a disaster, now restoring is a nightmare because we had to roll back the version, empty the cache, and regenerate everything", and all the other edge cases that no matter what you do, will cause cascading failures at a huge scale.
No matter what rule set you write, something's going to get past it.
Or, to put it another way, if you aren't yet on the Pareto frontier between power and safety, sure, by all means go get your free safety and power. But you will hit a limit on the two before you have all the power and all the safety, and the limit you will hit is going to be uncomfortable in at least one direction.
Somethings probably randomly fail, but because entropy and time is randomly selected we find ourselves in the successful ones.
You can't even easily ask "are you sure?" because if you're asking that for every little thing it ceases to be a useful guard. You need tools that detect if you're doing something stupid and dangerous and only ask then, but in the limit, that's strong-AI hard for ops people. That is, there are some obvious ones you can try to catch... "did you really mean to unassign all IP addresses?", but in general there's always something that will go wrong more cleverly than your detection code.
Hooking machines up to orchestration code is something I have to do. I operate at scales that Facebook would laugh at, but they're still well beyond what is practical to manually manage. In my opinion that scale taps out somewhere in the large single digits per ops person, which is nothing nowadays. But it always makes me nervous to do so, too, because I can see I'm putting all my eggs in one basket in the process, and the traditional "watch that basket really hard!" answer for when you're stuck in that situation is visibly not adequate.
I don't have a solution to propose. The tension seems fundamental to me. All I can suggest is that everyone sitting in front of any devops tool always be keeping the possibilities in mind, despite your brain's desires to say "hey, the last 1000 deploys went fine, I can stop being so vigilant about this one", and that any guard rails that can be added should be, even though they can never be 100% effective.