This question is asking about a generally applicable engineering principle. It is using T-SQL as a specific example, but the question is about the engineering principles behind major SQL dialects in general. It is also asked in the same spirit as this earlier question:
T-SQL's been around for a little while, and so has SQL Server, which it goes hand-in-hand with. A lot of T-SQL / SQL Server questions on Stack Overflow, for instance, pertain to the version from 2005. It is now 15 years later.
As of December 2020, there is still no simple, native
UPSERT statement. Instead what many people do is to simply check for existence first, then insert or update. However this has concurrency issues, unless you also specify locking. At that point, you have to be conscientious of the scope of different locking mechanisms, and your code becomes a bit long-winded.
Upsert is an operation that is constantly needed; however the decision to still not include it - years and years after T-SQL has been released, updated, and expanded upon - must be conscious and deliberate. There has been an abundance of time to add an
UPSERT keyword and native operation into T-SQL / SQL Server, so there's something Microsoft is seeing that's making them wary of doing it - some engineering principle that has to be taken into consideration for any major SQL dialect in general.
So what advantage is gained, from an engineering perspective, by not having an
UPSERT keyword/operation in a large-scale SQL dialect? What drawback would the inclusion of this bring into the equation? It can be an advantage for languages to remain relatively slim, only adding new features where there is a clear, lasting advantage. But since upsertion is such a commonly required and clearly defined operation, what other advantage is gained by deliberately leaving it out?
This is roughly along the lines of what I'm visualizing. It should be able to use essentially the same internal, automatic locking mechanisms that keep two
UPDATE or two
INSERT statements out of each other's way. SQL implementations already have to prevent
UPDATE statements from concurrently affecting the same row (or
INSERT statements from trying to insert the same automatic primary key value at the same time), so we should be able to just use either the exact same or nearly the same locking mechanisms here:
UPSERT [TableX] (ColumnA, ColumnB, ... ) VALUES (@valueA, @valueB, ... ); ↓ IF NOT EXISTS( SELECT 1 FROM TableX WITH ( <roughly the union of internal, automatic locks used with UPDATE and INSERT statements> ) WHERE (ColumnA = @valueA OR ISNULL(ColumnA, @valueA) IS NULL) AND (ColumnB = @valueB OR ISNULL(ColumnB, @valueB) IS NULL) AND ... ) INSERT TableX (ColumnA, ColumnB, ... ) VALUES (@valueA, @valueB, ... );
UPSERT [TableX] (ColumnA, ColumnB, ... ) VALUES (@valueA, @valueB, ... ) WHERE <SetOfConditions>; ↓ IF EXISTS( SELECT 1 FROM TableX WITH ( <roughly the union of internal, automatic locks used with UPDATE and INSERT statements> ) WHERE <SetOfConditions> ) UPDATE TableX SET ColumnA = @valueA, ColumnB = @valueB, ... WHERE <SetOfConditions> ELSE INSERT TableX (ColumnA, ColumnB, ..., <AnythingElseFrom_SetOfConditions> ) VALUES (@valueA, @valueB, ..., <AnythingElseFrom_SetOfConditions> );
To me this seems to follow naturally and logically, using roughly the same internal locking mechanisms that are used to implement
INSERT statements. The locking might be slightly more involved, because this is two operations back-to-back, but it should be 80-90% just a rehash of what's already in place for
INSERTs. It should primarily just be a unioning of
INSERT-style locks together.
The only slightly messy part would be the explicit
WHERE clause. On the one hand, you basically have to deterministically derive values from it, to implicitly pad them on to the
SELECT clause. This is so that in the case of insertion, the resulting row will match the
WHERE clause. Because of this,
WHERE conditions like
ColumnZ > 0 will not work without randomness and extra complication, but conditions like
ColumnZ = 0 or
ColumnZ IS NULL will work.
On the other hand, the conditions in the
WHERE clause should not be allowed to contradict the
SELECT clause. Once again, this has to do with insertion. If a new row has to be inserted, and if the clauses contradict each other, which clause is the new row supposed to match? Because of this issue, contradictions will need to invalidate the statement/operation.
So two things are needed to resolve the two issues above: For the first issue to be resolved,
WHERE conditions will need to be deterministic (unless you allow randomness and extra complication there). For the second issue,
WHERE conditions should not be allowed to contradict anything in the
To me, as long as the
WHERE clause is restricted enough to resolve the two issues above, this seems to make perfect sense, be extremely practical and commonly applicable, and have a solid longevity ahead of it. Be it T-SQL or anything else, why would a major, well-funded, full-scale SQL implementation continue to forgo this feature indefinitely? From an engineering perspective, what's the specific issue that makes this a serious problem?