Support references to UPDATE .. FROM tables from SET clause in dialects that don't support MERGE

[lukaseder]

jOOQ 3.17 introduced emulation support for UPDATE .. FROM using MERGE:

• Emulate UPDATE .. FROM with MERGE, where available #13326

jOOQ 3.20 added an additional type of emulation using the UPDATE .. ORDER BY .. LIMIT emulation, which is capable of emulating UPDATE .. FROM when no references to tables from the FROM clause are present in the SET clause:

• Add broader DELETE .. USING and UPDATE .. FROM support, using the same emulation as that for DML ORDER BY .. LIMIT #16633

This issue aims for adding emulation support also in the remaining cases for these dialects:

• Access
• ClickHouse
• Firebird 2.5 (3.0 has MERGE)
• MariaDB
• MemSQL
• MySQL
• Redshift
• SQL Data Warehouse
• SQLite 3.30 and earlier (3.38 has UPDATE .. FROM)
• Trino
• YugabyteDB

The emulation might look something like this:

Original

UPDATE t
SET t.a = u.b
FROM u
WHERE t.i = u.i
AND t.j = 1

Emulation

UPDATE t
SET t.a = (SELECT u.b FROM u WHERE t.i = u.i)
AND t.j = 1

It's likely not a very trivial transformation.