Updates and inserts by improving ON/GROUP By Clause-CodePudding

Here is the sample of the Source and target table. I only have one unique id called "Premise_ID". I would like to update the target table based on Premise ID. When i am running below mentioned SQL query then I am getting an error mentioned below. I am having multiple rows with the same Premise_ID but with different column attributes.if the first Premise_ID matches then update all the column attributes. if the second Premise_ID is same as previous one then just add another row with all the column attributes. Is there any way to overcome this error?

Source Table

Target Table

SQL Query:

USE GIS_NewJersey
GO
WITH Source AS (
    SELECT Premise_ID, Division, InstallationType
    FROM sde.SAP_Load_test
),
Target AS (
    SELECT Premise_ID, Division, InstallationType
    FROM sde.PREMISE_test
)
MERGE Target t
USING Source s
  ON t.Premise_ID = s.Premise_ID
WHEN MATCHED THEN
  UPDATE SET
    Division = s.Division,
    InstallationType = s.InstallationType
WHEN NOT MATCHED THEN
  INSERT (Premise_ID, Division, InstallationType)
    VALUES (s.Premise_ID, s.Division, s.InstallationType)
;

ERROR:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

CodePudding user response：

You can use 3 columns in MERGE SEARCH CONDITIONS and use WHEN NOT MATCHED BY TARGET to INSERT and WHEN NOT MATCHED BY SOURCE to DELETE

USE GIS_NewJersey
GO
WITH Source AS (
    SELECT Premise_ID, Division, InstallationType
    FROM sde.SAP_Load_test
),
Target AS (
    SELECT Premise_ID, Division, InstallationType
    FROM sde.PREMISE_test
)
MERGE Target t
USING Source s
  ON t.Premise_ID = s.Premise_ID
  AND t.Division = s.Division
  AND t.InstallationType = s.InstallationType
WHEN NOT MATCHED BY TARGET THEN
  INSERT (Premise_ID, Division, InstallationType)
    VALUES (s.Premise_ID, s.Division, s.InstallationType)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
;

CodePudding user response：

MERGE is problematic and unnecessarily complex for what is essentially a very basic "update if exists, otherwise insert" (or "UPSERT") problem. I have put together a page that links to several cautionary articles and alternative approaches:

So, you want to use MERGE, eh?

Here's how I recommend you handle your logic, using separate and easier to troubleshoot update and insert commands (MERGE gives the illusion of being one operation, but it's not):

BEGIN TRANSACTION;
 
UPDATE pt WITH (UPDLOCK, SERIALIZABLE) 
  SET Division = s.Division, 
      InstallationType = s.InstallationType
  FROM sde.PREMISE_test AS pt
  INNER JOIN sde.SAP_Load_test AS s
    ON pt.Premise_ID = s.Premise_ID;

INSERT sde.PREMISE_test(Premise_ID, Division, InstallationType)
  SELECT s.Premise_ID, s.Division, s.InstallationType
    FROM sde.SAP_Load_test AS s
    WHERE NOT EXISTS (SELECT 1 FROM sde.PREMISE_test
      WHERE Premise_ID = s.Premise_ID);
 
COMMIT TRANSACTION;

Example db<>fiddle

But there's a problem, because it's unclear if you want to end up with multiple rows for any given Premise_ID in the target table (while you say one unique id called "Premise_ID", other things you're saying suggest you want multiple rows). In the fiddle, the row in the target for 8000004592 has been updated with an arbitrary row from the source (in reality, it was updated three times, this was just the "last" one - in some nondeterministic order). You also don't explain what you want to happen if there were two values for 8000004594 in the source - again, which row should it pick, if there is a unique constraint at the target? A table is an unordered bag of rows, so the concepts of "first" and "last" and "next" and "previous" don't make any sense unless you give it some deterministic meaning.

If you're going to ignore this advice and continue to use MERGE, even after reading (or not reading) all of the links, at the very least please put HOLDLOCK on the target.