On gists

MySQL can't specify target table for update in FROM clause

MySql MySql tricks MySql - advanced

super-workaround.sql Raw #

/*
  does not work!
*/

update Users set Valid = 0
where Id in (
  select Id from Users where Id < 100
)


/*
  workaround
  + one subselect more like a wrapper ;-)
*/

update Users set Valid = 0
where Id in (
  select Id from (
    select Id from Users where Id < 100
  ) as t
)

solutions-by-GPT.sql Raw #

/* temp table */
CREATE TEMPORARY TABLE temp_table
SELECT *
FROM your_table
WHERE your_condition;

UPDATE your_table
SET your_column = your_value
WHERE your_table.primary_key_column IN (
    SELECT primary_key_column
    FROM temp_table
);


/* Join - best way */
UPDATE Users
JOIN (
  SELECT Id
  FROM Users
  WHERE Id < 100
) AS t ON Users.Id = t.Id
SET Valid = 0;