[SOLVED] Data-modifying statement in WITH clause seems to run twice

Data-modifying statement in WITH clause seems to run twice

with i as (
    insert into llx_facture_fourn (ref_supplier)
    select seriesaa from noninsertedmydatainvoices
    returning rowid
)

update llx_facture_fourn set ref = concat('(MYDATA ', a.rowid, ')')
where rowid in (select rowid from i);

The above code, using query nonInsertedMydataInvoices finds non existing invoices and inserts them in the table, but we need to update the ref field. It seems that the query "i" runs twice and thus the second time has no invoices to insert and thus it does not update the "ref" field.

Is there a correct way of doing it, is it a bug ?

Solution

In addition to Adrian Klaver's comment, the exact sentence from the doc that prevents you from updating the just inserted rows is:

All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables.

It means that the main update sees llx_facture_fourn as it was snapshot before entering the CTE, thus without the just added rows.

Two modifying statements in a CTE will never be able to interact with one another's effects on the database.

Adrian Klaver is right, the right (and simplest) way to handle this is through a trigger.
Or you can make them one insert (where you manually generate a rowid and its embedding ref).