UPDATE queries - MarkMpn/Sql4Cds GitHub Wiki

SQL 4 CDS translates UPDATE queries to a set of standard update requests that are processed by Dataverse / Dynamics 365. It does not perform any unsupported modifications directly to the underlying SQL database.

The supported syntax is the same as SQL Server with the exception that the OUTPUT clause is not supported.

If you are updating multiple records in the same way it is much more efficient to do so in a single query rather than have multiple queries each updating a single record by ID - this allows SQL 4 CDS to take advantage of the parallelism featres to update your records quicker.

Even if a record already has the value that the UPDATE query is setting a field to, the update request will still be sent to the server to ensure any plugins are fired that might cause additional changes. If this is not required, be sure to exclude any records that already have the new values to reduce the number of records that have to be updated.

UPDATE account
SET    name = 'Data8'
WHERE  accountid = 'D2F952E0-2458-4723-AA26-C42EA1BE5553'

The record with the given ID (if it exists) will be updated with the new name.

UPDATE account
SET    industrycode = 2
WHERE  industrycode = 1
       AND statecode = 0

All active records with the old industry code will be updated to use the new code.

UPDATE account
SET    name = 'Data8'
FROM   account
       INNER JOIN
       contact
       ON account.accountid = contact.parentcustomerid
WHERE  contact.fullname = 'Mark Carrington'

All accounts that have a matching contact will be updated with the new name. Even if an account has two matching contacts, each account record will only be modified once.

UPDATE account
SET    turnover = turnover + 1000
WHERE  name = 'Data8'

UPDATE account
SET    turnover += 1000
WHERE  name = 'Data8'

Both these queries are equivalent - they will increase the turnover value of each matching record by 1000. Any supported expression can be used for the right hand side of the = in the SET clause.

UPDATE account
SET    industrycode = 2,
       description = 'Changed industry code'
FROM   account
WHERE  industrycode = 1

Modifies the industry code and sets the description on the affected records. Modifying both fields at the same time is more efficient and less error prone than trying to get the same effect in two separate queries.

UPDATE account
SET    turnover = 1000000
WHERE  name = 'Data8'
       AND (turnover IS NULL OR turnover <> 1000000)

By including a condition in the WHERE clause to ensure records that already have the new value are not included in the update, records that don't need to be changed will not be touched and so any plugins registered on the update of that field will not be triggered.

UPDATE uat
SET    name = dev.name
FROM   [UAT Instance].dbo.account AS uat
       INNER JOIN
       [DEV Instance].dbo.account AS dev
       ON uat.accountid = dev.accountid

Using a cross-instance query, this copies the names of accounts from a development instance to a UAT instance ready for testing.