Is this the only way to use MERGE in Oracle database?

Question

I am moving my first step with Sql Server database projects and I was searching for a way to populate reference tables in post deployment scripts without deleting and refilling them each time. After some research I stumbled upon this blog entry:

http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx

That basically describes the behaviour of the MERGE statement with this example:

-- Reference Data for AddressType
MERGE INTO AddressType AS Target
USING (VALUES
  (0, N'Undefined'),
  (1, N'Billing'),
  (2, N'Home'),
  (3, N'Main Office'),
  (4, N'Primary'),
  (5, N'Shipping'),
  (6, N'Archive')
)
AS Source (AddressTypeID, Name)
ON Target.AddressTypeID = Source.AddressTypeID
-- update matched rows
WHEN MATCHED THEN
UPDATE SET Name = Source.Name
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (AddressTypeID, Name)
VALUES (AddressTypeID, Name)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE

Since I work also with Oracle databases I have tried to adapt the previous sample to Oracle 10g syntax and I've produced something like this:

-- Reference Data for AddressType
MERGE INTO AddressType AS T
USING (WITH CTE AS (
  SELECT 0 AS AddressTypeID, 'Undefined' AS Name FROM DUAL
  UNION 
  SELECT 1 AS AddressTypeID, 'Billing' AS Name FROM DUAL
  UNION
  SELECT 2 AS AddressTypeID, 'Home' AS Name FROM DUAL
  UNION
  SELECT 3 AS AddressTypeID, 'Main Office' AS Name FROM DUAL
  UNION
  SELECT 4 AS AddressTypeID, 'Primary' AS Name FROM DUAL
  UNION
  SELECT 5 AS AddressTypeID, 'Shipping' AS Name FROM DUAL
  UNION
  SELECT 6 AS AddressTypeID, 'Archive'AS Name FROM DUAL
)
SELECT AddressTypeID, Name FROM CTE
)
AS S 
ON T.AddressTypeID = S.AddressTypeID
-- update matched rows
WHEN MATCHED THEN
UPDATE SET T.Name = S.Name
-- insert new rows
WHEN NOT MATCHED 
INSERT (T.AddressTypeID, T.Name)
VALUES (S.AddressTypeID, S.Name)

Besides not being perfectly equivalent (no delete for rows not in source) I find the syntax particularly ankward.

There are any syntactic shortcuts available for Oracle SQL?

Answer 1

I don't see any shortcut, the syntax for MERGE is what you have with you.

The only thing you could get rid off is the WITH clause i.e. CTE in the USING clause. You could simply put it as:

-- Reference Data for AddressType
MERGE INTO AddressType AS T
USING (
  SELECT 0 AS AddressTypeID, 'Undefined' AS Name FROM DUAL
  UNION 
  SELECT 1 AS AddressTypeID, 'Billing' AS Name FROM DUAL
  UNION
  SELECT 2 AS AddressTypeID, 'Home' AS Name FROM DUAL
  UNION
  SELECT 3 AS AddressTypeID, 'Main Office' AS Name FROM DUAL
  UNION
  SELECT 4 AS AddressTypeID, 'Primary' AS Name FROM DUAL
  UNION
  SELECT 5 AS AddressTypeID, 'Shipping' AS Name FROM DUAL
  UNION
  SELECT 6 AS AddressTypeID, 'Archive'AS NAME FROM DUAL
      ) AS S 
ON T.AddressTypeID = S.AddressTypeID
-- update matched rows
WHEN MATCHED THEN
UPDATE SET T.Name = S.Name
-- insert new rows
WHEN NOT MATCHED 
INSERT (T.AddressTypeID, T.Name)
VALUES (S.AddressTypeID, S.Name)

And, of course, you cannot update the column mentioned in the ON clause.