Applying SQL UPDATE to Dataverse tables, How?

[Walad100100]

I come from the RDBMS world where I can update the data of any given table using the classic SQL UPDATE statement.  How to do so for any Dataverse Table? I spent weeks searching for a mechanism to do so and I failed. All I found is how to use Power BI and Power Query to extract information (including dataflow). 

I know that I can edit a Dataverse table using Excel directly, but I need an advanced mechanism such as the UPDATE statement that enables me to specifically select what rows to update.

Your help, please.

[GuidoPreite · Accepted solution]

You can't execute UPDATE SQL statements against Dataverse, only the READ is supported by the TDS endpoint.

There is a XrmToolBox tool called SQL 4 CDS that will convert and execute your SQL statement to the Dataverse API

[Walad100100]

@GuidoPreite Thank you for your prompt response and effective answer.

I am really puzzled:

1. Why such a fundamental need for modifying Dataverse tables is NOT provided natively by Microsoft especially that most developers are coming from the RDBMS world. 

2. In Microsoft's documentation, their focus is entirely on the READ-ONLY mode where we can use Power BI and SSMS, etc. which is not realistic for developing even a silly app. We will always need to mass-update data for one reason or another. 

3. Is there another mechanism other than SQL 4 CDS (which is not from Microsoft)?

4. This question was not raised in this forum before, which makes me question myself and whether I am following the best practices with Dataverse.

Advise, please. -- Mega Thanks! 

2. 

[GuidoPreite]

1. Because Dataverse comes from Dynamics CRM (the current model-driven apps) where there is the concept of supported and unsupported customizations. With supported customizations Microsoft guarantees model-driven apps work (because the Dataverse layer is how they intended) if they allow ALTER, UPDATE or DELETE sql statements there is an high possibility that model-driven apps stop to work, so they consider this unsupported customizations

2. "which is not realistic for developing even a silly app", if you are developing a canvas app you can use a SQL DB source directly, you don't need Dataverse, Dataverse comes with its own advantages (like the security layer and roles for accessing the data) that you will need to build in your SQL database. Canvas apps can use different data sources, model-driven apps are based on Dataverse.

3. No, there isn't another tool. Keep in mind that SQL 4 CDS converts your SQL statement to supported API calls for Dataverse, it does not execute SQL queries directly (except for the read operation if the TDS endpoint is enabled).

[Walad100100]

Highly informative indeed -- Thanks.

[soccer]

I am in full agreement: Microsoft: IMPLEMENT a way for developers to UPDATE a Dataverse table with something like an SQL UPDATE statement!!