Incorrect Update Results

[tsileo-wdi]

Very strange situation here - I've tried structuring this update a few different ways, and I'm getting the same results.

Here's my query:

update wdi_ivueworkordersimported
set wdi_importstatus = 'DONE'
where wdi_woid in (
    select wdi_woid from (
        select wi.*,
        (select top 1 wdi_sourcehash from wdi_ivue_wo_mstr_view wo where wo.wdi_name = wi.wdi_woid) as sourcehash
        from wdi_ivueworkordersimported wi
        where wi.wdi_importstatus = 'STALE') t
        where t.sourcehash = t.wdi_hashstring
);

If run the select in the in () clause, it returns 43 rows:

when I run the update, it says it will update 43 rows:

I hit yes and it says it updated 43 rows:

but the table still has 43 records marked STALE:

wdi_ivueworkordersimported is a virtual table held in SQL Server. wdi_ivue_wo_mstr_view is a dataverse table.

I also have several other tables following the same pattern, and my update for those tables works as expected (set wdi_importstatus = DONE when the hashstring values match).

here's the execution plan from the update in case that helps:

[MarkMpn]

Thanks for all the details!

I’ve recently identified an error with virtual tables where they appear to support the newer CreateMultiple and UpdateMultiple messages but really don’t (#541) - can you please check if you get any different results if you set the batch size to 1 to force it to use the standard Update message?

[tsileo-wdi]

Yep - that gets it to work, although now I hit the API rate limit after about 100 updates.

[MarkMpn]

Interesting, can you post the details of the error you get please? It should normally be handled with automatic retries by the underlying service client.

[tsileo-wdi]

Sure thing - see attached!
SQL4CDSError.txt