Updating Related Table


#1

When i try to update field in a related table with two foreign keys (set as primary keys) i get this message.

  "error": [
    {
      "context": null,
      "message": "Failed to update many to one assignment.\nRelating records with multiple field primary keys is not currently supported.",
      "code": 400
    }
  ]
}```

The related table has two keys, one timestamp field and one "comments" field
I would like to be able to : 

1: update timestamp and comment field for old entries
2: create new entry if there are none of that kind there (meaning key1 and key2 are not there)

so how can i do this? 
Server side or API, does not matter, as long as i can get it done ;)

EDIT: I have full access to the MSSQL server but i can not change the schema, i can make custom functions if that is needed also.

Get or Post from/to a Pivot Table
#2

Here is an update on how to do many to one assignment with multiple primary keys.

you need to do this as a SQL Procedure and in my example i have a table with two primary keys, one timestamp field and one comment field.

CREATE PROCEDURE [dbo].[NAMEOFPROCEDURE] -- Add the parameters for the stored procedure here @KEY1 identitet, @KEY2 identitet, @COMMENTS varchar(1024) AS BEGIN declare @DATETIMEINFO datetime set @DATETIMEINFO = GETDATE() MERGE [TARGET_TABLE] AS Target USING (select @KEY1,@KEY2,@DATETIMEINFO,@COMMENTS) AS Source (Key1,KEY2,DATETIMEINFO,COMMENTS) ON (Source.KEY1 = Target.KEY1 AND Source.KEY2 = Target.KEY2) WHEN MATCHED THEN UPDATE SET Target.DATETIMEINFO = Source.DATETIMEINFO, Target.COMMENTS = Source.COMMENTS WHEN NOT MATCHED BY TARGET THEN INSERT ([KEY1], [KEY2], [DATETIMEINFO], [COMMENTS]) VALUES(Source.KEY1, Source.KEY2, Source.DATETIMEINFO, Source.COMMENTS); END

MY app.js file

window.app = {}; document.addEventListener("apiReady", function(){ window.app.updateTable = function (KEY1,KEY2,COMMENTS){ var item = {"params": [{"name": "KEY1","value": KEY1},{"name": "KEY2","value": KEY2},{"name": "COMMENTS","value": COMMENTS}]}; window.df.Ortopedi.callStoredProcWithParams({"procedure_name":"NAMEOFPROCEDURE", "body":item}); }; }, false);

Then i just call the function with

window.app.updateTable(KEY1,KEY2,COMMENTS)

Hope this helps anyone in need.

I am also planning on using the same procedure to delete records as well, adding a fourth parameter to the call, but that is for a later time.