Database Upserts - Good or Bad Practice?

The primary problem is overwriting an existing record when the intention is to add a new record because whatever was selected as the key is duplicated. Say a login name for example. You see that login exists so you update when you should have kicked back an error that the login is a duplicate.

A second problem is with resurrecting a deleted record. Say process "A" queries the record, process "B" deletes it, and then process "A" submits a change. The record that was intended to be deleted is now back in the database rather than passing an exception back to "A" that it was deleted.

I like to program on purpose.

Either I'm creating something, in which case I would want the insert to fail (duplicate) if there already was a entity there. Or, I'm updating something that I know is there, in which case I'd like the update to fail (which actually doesn't happen).

With upsert/merge this get kind of fuzzy. Did I or did I not succed? Did I partially succeed? Some of the values in the row are mine (from insert) and some of them was there before?

Having said that, Upserts are useful (which is why they were implemented to begin with) and banning them would be just silly. That's like banning roads because criminals use them to get away from the cops. There are an infinite number of cases where upserts are the only resonable way of doing things. And anyone who have worked with synchronizing of data between systems knows this.

Depends what you talk about. Data? Well, that is determined by data manipulation processes, or? If I need to insert OR update, then I need to do that. if it is about schema objects, similar.