Updating Related Table

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.

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.

1 Like