Update a row in SQL Server fails, because the ID for update is sent with update fields request

[rodrigosaraiva]

First, it is some imcompreensible that Flow doens't have an update component allows to update a row with another fields beyond the id field. When we do complex logics with data, this is necessary.

Second, I am trying to update a row, after be obligated to go field by field, to check some condition, and when the condition is reached, and I call the update step, an error occurs because the request to update is sending the ID in the group fields that I want to update.

I think it is a component error, and nothing that could be fixed with alternate paths.

[v-yamao-msft]

Hi rodrigosaraiva,

What’s the trigger of your flow? How is the id field for action “SQL – Update row” configured?

To test this issue, I create a simple flow. The flow is triggered by “SharePoint – when a new item is created”, followed by the action “SQL – Update row”.

Then create a new item to trigger the flow, if the id is already existing in SQL table, it will run succeed.

Screenshots for the flow configuration and run details:

Please show me more details to reproduce this issue on my side.

Best regards,
Mabel Mao

[rodrigosaraiva]

Thank for your repply.

but the problem is: I have a different structure from Sharepoint List and my database model, and this thing I can't change, because the database is receiving data from other diferent systems.

My trigger is an existing item is modified, then I get the item, I compose an external_id (it is how I need to control the integration between the list and database table, because the ID of a list cannot be the ID of table, and this is the reason that I complain because I cannot get a table row by another field different from table ID), then I get all the rows from table, and trying to identify the table row by a condition, to do an update. Everything is right, but when the update is sent to database server an error occurs because the ID field is sent to be updated to with the modified fields, and SQL Server don't allow update the ID field. I think the error is in this point.

[v-yamao-msft]

Hi rodrigosaraiva,

Thanks for clarification with more details.

As you mentioned, SQL Server don't allow update the ID field, so you will not be able to update the row with the modified ID field.

Best regards,
Mabel Mao

[rodrigosaraiva]

I think you do not understand the problem yet. I am not sending the Id field for update. I don't want modify the id field.

The component is doing that. That is the problem.

This update row component is sending the id for update, when is suposed to use the id only to identify the row.

thanks

[jakekemme]

I am having this same issue.  I'm attempting to update a row where the ID is an Identity Specification (numeric id auto incremented by the sql server).  The flow 'Update Row' action attempts to update this ID as well, which seems to be a bug as the selected row identity should never be updated.  As it stands, it seems impossible to update rows that have sql generated row identities as the primary key.

[NieGreJoh]

Hi - I have also faced the same problem - trying to document how I compensated for that https://blog.hexatown.com/creating-sql-table-for-use-in-flow-and-powerapps-dd9e637caaf3

[Anonymous]

Hi, I'm also hitting the same problem. The identity column should not be passed along to the update or it should include set identity on during update.

Anyone has the solution? Without the fix, the SQL update row totally cannot be used.

[asdeev]

Is there a solution for this?

[jarodts]

Ugh - no fun to find a problem with no solution...

[jarodts]

Found this and used the uniqueidentifier data type with NEWSEQUENTIALID

https://community.dynamics.com/crm/b/crmchap/posts/getting-around-sql-server-table-restrictions-micr...

https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-...

Not elegent but works