Merge Insert

When accessing data from a remote system that acts as the "system of record" for certain data, it is often desirable to acquire the data and:
  • insert new local rows (if the remote data is new), or

  • update local rows (if the remote data has already been inserted, but needs to be update)

Merge-Insert support provides this function.  It is sometimes call "upsert".

via SysUtility.restPut interface, you can use options in the @metadata tag (more here), like this:

// insert or update
{"@metadata" : {"action":"MERGE_INSERT", "key":"name"}, "name":"David 1", "some_val":199}

// insert or update using id field, name field updated
{"@metadata" : {"action":"MERGE_INSERT", "key":"id"}, "id": 10, "name":"David 2", "some_val":199}

// insert or update using multiple fields id and name field updated using an array of field names
{"@metadata" : {"action":"MERGE_INSERT", "key":["id","name"]}, "id": 10, "name":"David 2", "some_val":199}

// insert or update using primary key (in this case id), name field updated
{"@metadata" : {"action":"MERGE_INSERT"}, "id": 10, "name":"David 2", "some_val":199}


{"@metadata" : {"action":"MERGE_INSERT", "key":"name"}, "name":"David 2", "some_val":200}

// delete, using name field for unique key
{"@metadata" : {"action":"DELETE", "checksum":"override", "key":"name"}, "name":"David 2", "some_val":202}





create table test1 (
    id int auto_increment primary key
  , name varchar(20) not null
  , some_val varchar(10)
);

create table test1_child (
    ident int auto_increment primary key
  , test1_id int not null
  , ordnum varchar(10) not null
  , ordtext varchar(20) 
  , constraint fk_test1_1 foreign key (test1_id) references test1(id)
);




{"@metadata" : {"action":"MERGE_INSERT", "key":"name"},
   "name":"David 1-updated-3",
   "some_val":202,
   "Child": [
      {"@metadata": {"key":["test1_id", "ordnum"]},
       "ordnum": "ORD1"
       }
   ]
}

{
  "statusCode": 200,
  "txsummary": [
    {
      "@metadata": {
        "href": "http://localhost:8080/APICreator/rest/abl/demo/v1/Test.Child/1",
        "resource": "Test.Child",
        "verb": "INSERT",
        "checksum": "A:c75fb1e4368499a8"
      },
      "ident": 1,
      "test1_id": 13,
      "ordnum": "ORD1",
      "ordtext": null
    },
    {
      "@metadata": {
        "href": "http://localhost:8080/APICreator/rest/abl/demo/v1/Test/13",
        "resource": "Test",
        "verb": "INSERT",
        "checksum": "A:2d99275abd5ebcf2"
      },
      "id": 13,
      "name": "David 1-updated-3",
      "some_val": "202"
    }
  ]
}

{
  "@metadata": { "action": "MERGE_INSERT", "key": "name" },
  "name": "David 1-updated-3",
  "some_val": 202,
  "Child": [
    { "@metadata": { "key": ["test1_id", "ordnum"] }, "ordnum": "ORD1", "ordtext": "some random text" }
  ]
}

{
  "statusCode": 200,
  "txsummary": [
    {
      "@metadata": {
        "href": "http://localhost:8080/APICreator/rest/abl/demo/v1/Test.Child/1",
        "resource": "Test.Child",
        "verb": "UPDATE",
        "checksum": "A:2062d5cdcac606c9"
      },
      "ident": 1,
      "test1_id": 13,
      "ordnum": "ORD1",
      "ordtext": "some random text"
    }
  ]
}