Update colPos with typo3 extension flux 9.0.1

Question

Since the update flux to 9.0.1 I need to update the colPos of elements.

This works fine:

UPDATE `tt_content` 
SET colPos = ((tx_flux_parent * 100) + 11) 
WHERE tx_flux_column = "content";

But I need also to update the localized content elements. It have in tx_flux_parent the localized parent uid. But I need the parent uid of the standard language.

I need to get the value "tx_flux_parent" in tt_content by l18n_parent. So I'm trying to build a query with l18n_parent like this:

UPDATE `tt_content` as t1 
SET colPos = (( (SELECT t2.tx_flux_parent 
                 FROM tt_content t2 
                 WHERE t1.l18n_parent = t2.uid) * 100) + 11) 
WHERE t1.tx_flux_column = "content";

And get this:

MySQL meldet: Dokumentation 1093 - Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Answer 1

MySQL does not allow referencing the table being updated again in another subquery, unless it is inside the FROM clause (Derived Table).

However, in this particular case, you can rather use "Self-Join":

UPDATE `tt_content` as t1 
JOIN `tt_content` as t2 
  ON t1.l18n_parent = t2.uid 
SET t1.colPos = ((t2.tx_flux_parent * 100) + 11) 
WHERE t1.tx_flux_column = 'content'