IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • No replies
ERF
ERF
5 Posts

Pinned topic Insert/update using MERGE and a subset of columns

‏2019-05-02T06:53:38Z | merge sql

When I test this code using a table with three columns, it works.  When I test it against the production table with 100 columns, it fails with SQLSTT=42802.

MERGE INTO ARP001 USING (VALUES ('K2', 'OFFICE K2', 'K2')) 
NewData (CMCUST, CMNAME, CMTID)
ON ARP001.CMCUST = NewData.CMCUST
WHEN NOT MATCHED THEN
  INSERT VALUES(NewData.CMCUST, NewData.CMNAME, NewData.CMTID)     
WHEN MATCHED THEN 
  UPDATE SET CMNAME = NewData.CMNAME, CMTID = NewData.cmtid;

The error text states, "If any columns not specified in the INSERT column list were created as NOT NULL, specify values for those columns."   Since this table is created with NOT NULL, it appears I can't use MERGE for a one-statement add/update operation unless I provide defaults for the 97 unreferenced columns.  Is this interpretation correct?

I'm trying to figure out an alternate solution that will work in high-concurrency environment (think inventory transactions).  Unexpected activity can slip between an UPDATE and an INSERT, so I suspect something like SET TRANSACTION ISOLATION LEVEL SERIALIZABLE will be necessary to make things work properly.

I'd appreciate any solutions to the insert/update process!

Thanks,

rf