cancel
Showing results for 
Search instead for 
Did you mean: 

How to update a record Using SQL SERVER UPDATE without updating identity field

RoyB
New Contributor III

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

1 ACCEPTED SOLUTION

RoyB
New Contributor III

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

View solution in original post

2 REPLIES 2

RoyB
New Contributor III

With some more investigation, I've found that the tables we have been updating up till now are not declared as IDENTITY in SQL, but rather the third party that created the system have them working functionally as IDENTITY fields, but rather than let SQL maintain this, they do it in the backend of there system

The new system I am currently trying to integrate too uses IDENTITY declarations, which implies that the way we have been updating has been wrong

Any assistance with what the correct way is, would be appreciated.

RoyB
New Contributor III

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