How to update a record Using SQL SERVER UPDATE without updating identity field
The process established by the company I work for is that we have the document coming into a MAPPER, that links the incoming fields to the SQL SERVER UPDATE Snap. This tells the UPDATE snap which fields should receive which values, but this includes the identity field. I cannot exclude the identity field, because then the update fails because it does not have a value to compare in the where clause
To put this into a SQL context:
What I want to do:
UPDATE table
SET FIELD = value
WHERE ID = id
What the Mapper + Update is doing:
UPDATE table
SET FIELD = value
AND ID = id
WHERE ID = id
And if I remove the ID field from the Mapper, then the pipeline fails because the field doesn't exist, but esentially I am trying to tell SQL to:
UPDATE table
SET FIELD = value
WHERE ID = null
This has only occurred on 1 of our servers, which is how I've discovered that the ID field is being updated on the other servers. We had assumed that Snap knew not to update the ID field.
I am investigating SQL to see how the other servers are able to allow this on IDENTITY fields
But I am posting here, because I am concerned that we are going about doing the updates, incorrectly
Thanks for any assistance anyone is able to provide
figured it out. the ID field needs to be assigned to $original.ID
The "orginal", which is case sensitive, lifts it into a sub-document in the json
which is then ignored by the SET, but you are still able to include it in the WHERE
(I had tried this already, but had typed it as Original, which didn't work, but revisited it once I found sometihng in the help for the SQL SERVER UPDATE Snap