Why upsert is not a fundamental SQL operation [closed]

Question

Why don't SQL support the upsert use case? I am not asking how to do in a particular db(*). What I want to know is why upsert is not a fundamental operation like insert and update. I mean it is a pretty straightforward use case right? I guess there must be some fundamental db first principle that gets broken when doing upsert or some technical challenge that the db engine faces when confronted with a upsert.

*. I know about the mysql syntax and the SQL Merge syntax. BTW even while using such db specific syntax you need to be careful about atomicity and locking. And using the merge syntax, it doesn't feel right having to create a psuedo table.

Edit: I am editing this to clarify that I am not asking an opinion. So I dont think this question should be blocked.

Answer 1

Because it isn't easily handable, both acid and syntax-wise.
The conditions for update if exists isn't clear.

For example, replace "insert into" with upsert in the below query

insert into t_something 
select * from t_whatever

No foreign keys, no primary keys.
How do you want to update ?
Would the where condition be for the select, or for the update ?

Ultimately, you have to write the condition, and then you can just as well do a "update/insert if"...

Usually, when you're asking yourself the upsert question, you're handling inserting/updating wrong.
You're thinking in object terms instead of set terms.

You want to loop through an array of objects, and insert if count(*) on exists is 0 else update.

That's how object-oriented imperative programming works, but that's not how SQL works.

In SQL, you operate with a SET.
You can easily do a inner join - update on the SET
and a left join where null insert on the same SET.
That's just as comfortable as a merge, and a lot more readable plus simpler to debug.
And it might well be faster.

You can already ensure it's all atomic by putting update & insert into a transaction.

Thinking of upsert, which idiotism do you want next ? "UpSertLeteTrunc" ? MerDel ?
Or perhaps truncsert ?

There are more important things to do, by far.

This is how I do Upsert with MERGE on SQL-Server:

-- How to create the XML 
/*
DECLARE @xml XML 
SET @xml = ( SELECT (SELECT * FROM T_Benutzer FOR XML PATH('row'), ROOT('table'),  ELEMENTS xsinil) AS outerXml )
-- SELECT @xml 
*/


DECLARE @xml xml 
SET @xml = '<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <PLK_UID>7CA68E6E-E998-FF92-BE70-126064765EAB</PLK_UID>
    <PLK_Code>A2 Hoch</PLK_Code>
    <PLK_PS_UID>6CF3B5AB-C6C8-4A12-8717-285F95A1084B</PLK_PS_UID>
    <PLK_DAR_UID xsi:nil="true" />
    <PLK_Name_DE>Mit Legende</PLK_Name_DE>
    <PLK_Name_FR>Avec Légende</PLK_Name_FR>
    <PLK_Name_IT>Con Leggenda</PLK_Name_IT>
    <PLK_Name_EN>With Legend</PLK_Name_EN>
    <PLK_IsDefault>0</PLK_IsDefault>
    <PLK_Status>1</PLK_Status>
  </row>
</table>'


DECLARE @handle INT  
DECLARE @PrepareXmlStatus INT  

EXEC @PrepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @XML


;WITH CTE AS 
(
    SELECT 
         PLK_UID
        ,PLK_Code
        ,PLK_PS_UID
        ,PLK_DAR_UID
        ,PLK_Name_DE
        ,PLK_Name_FR
        ,PLK_Name_IT
        ,PLK_Name_EN
        ,PLK_IsDefault
        ,PLK_Status
    FROM OPENXML(@handle, '/table/row', 2) WITH 
    (
         "PLK_UID" uniqueidentifier 'PLK_UID[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_Code" character varying(10) 'PLK_Code[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_PS_UID" uniqueidentifier 'PLK_PS_UID[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_DAR_UID" uniqueidentifier 'PLK_DAR_UID[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_Name_DE" national character varying(255) 'PLK_Name_DE[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_Name_FR" national character varying(255) 'PLK_Name_FR[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_Name_IT" national character varying(255) 'PLK_Name_IT[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_Name_EN" national character varying(255) 'PLK_Name_EN[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_IsDefault" bit 'PLK_IsDefault[not(@*[local-name()="nil" and . ="true"])]'
        ,"PLK_Status" int 'PLK_Status[not(@*[local-name()="nil" and . ="true"])]'
    ) AS tSource 

    WHERE (1=1) 
    
    -- AND NOT EXISTS 
    -- (
    --  SELECT * FROM T_VWS_Ref_PdfLegendenKategorie 
    --  WHERE T_VWS_Ref_PdfLegendenKategorie.PLK_UID = tSource.PLK_UID 
    --)
)
-- SELECT * FROM CTE     
MERGE INTO T_VWS_Ref_PdfLegendenKategorie AS A 
USING CTE ON CTE.PLK_UID = A.PLK_UID 
WHEN MATCHED 
    THEN UPDATE
        SET  A.PLK_Code = CTE.PLK_Code
            ,A.PLK_PS_UID = CTE.PLK_PS_UID
            ,A.PLK_DAR_UID = CTE.PLK_DAR_UID
            ,A.PLK_Name_DE = CTE.PLK_Name_DE
            ,A.PLK_Name_FR = CTE.PLK_Name_FR
            ,A.PLK_Name_IT = CTE.PLK_Name_IT
            ,A.PLK_Name_EN = CTE.PLK_Name_EN
            ,A.PLK_IsDefault = CTE.PLK_IsDefault
            ,A.PLK_Status = CTE.PLK_Status
WHEN NOT MATCHED BY TARGET THEN 
INSERT 
(
     PLK_UID
    ,PLK_Code
    ,PLK_PS_UID
    ,PLK_DAR_UID
    ,PLK_Name_DE
    ,PLK_Name_FR
    ,PLK_Name_IT
    ,PLK_Name_EN
    ,PLK_IsDefault
    ,PLK_Status
)
VALUES
(
     CTE.PLK_UID
    ,CTE.PLK_Code
    ,CTE.PLK_PS_UID
    ,CTE.PLK_DAR_UID
    ,CTE.PLK_Name_DE
    ,CTE.PLK_Name_FR
    ,CTE.PLK_Name_IT
    ,CTE.PLK_Name_EN
    ,CTE.PLK_IsDefault
    ,CTE.PLK_Status
)
-- WHEN NOT MATCHED BY SOURCE THEN DELETE
;


EXEC sp_xml_removedocument @handle