0

How can i write in MySQL

with temp as (select id from examples JOIN examples2 ON ... )
UPDATE orders SET value = 0 WHERE orders.id IN (select id from temp)

Imagine a huge subquery whose result is stored in temp and then used for further query and updates.

In MySQL with temp is not supported. How can I achieve the same result?

Revious
  • 7,816
  • 31
  • 98
  • 147
  • You can use stored procedures for this. http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx – Prabhjot Singh Kainth Nov 29 '19 at 10:39
  • Ok, I had this suspect.. I'd like to use an anonimous script. But I need to understand if I can define a table variable.. – Revious Nov 29 '19 at 10:44
  • 2
    I'd suspect most ctes can relatively easy be rewritten as a single non-cte query, but apart from creating a temporary table in a separate step, there is no general way that would work in all situations. In your example, you can just put the code into your subquery directly, but for other cases, you might need other solutions. Most promintently are problems around anything trying to simulate [recursive ctes](https://stackoverflow.com/q/20215744). – Solarflare Nov 29 '19 at 10:56
  • I've tried https://www.db-fiddle.com/f/rkkBkXAGKJHPMayFKLBrjB/0 but it gives an error.. I cannot understand why... – Revious Nov 29 '19 at 11:12

0 Answers0