Updating a Table using a CTE

Question

I asked a similar question regarding Oracle, but every DBMS is different.

I have a sample table at https://dbfiddle.uk/i2wN57xq . I would like to update the table using data from a CTE. Something like this:

with vars as (select 5 as n from dual)
update test set data=data*vars.n, more=more*vars.n;

This doesn’t work, of course. How can I use data from a CTE in an UPDATE statement?

Note:

• I know the sample is trivial, but I’m just looking for the correct technique.
• Most of my searches result in attempts to update the CTE itself. I don’t want to do that, just use the CTE for its data.

I’ve found a solution for PostgreSQL and SQL Server. For Oracle, I ended up with a subquery, which is fine, since I can get multiple results from the subquery. I would settle for something like that if it won’t work with a CTE.

Answer 1

You can CROSS join the table to the CTE in the UPDATE statement for MySql:

WITH vars AS (SELECT 5 AS n)
UPDATE test t 
CROSS JOIN vars v
SET t.data = t.data * v.n, 
    t.more = t.more * v.n;

See the demo

For MariaDB:

UPDATE test t 
CROSS JOIN (
  WITH vars AS (SELECT 5 AS n)
  SELECT * FROM vars 
) v
SET t.data = t.data * v.n, 
    t.more = t.more * v.n;

See the demo.

Or simpler (without the CTE):

UPDATE test t 
CROSS JOIN (SELECT 5 AS n) v
SET t.data = t.data * v.n, 
    t.more = t.more * v.n;

See the demo.