'mysql syntax assistance (for postgres query)

can you please advice the correct syntax for mysql 8 which equivalent to postgres following query:

IF EXISTS (select 1 FROM aggregator.argument where argumentname = 'DEFAULT_FE_PAGE' AND value = 'NEW')
THEN
update aggregator.argument set value='AA' where name = 'DEFAULT_FE_PAGE';
END IF;

thanks

mysql


Solution 1:[1]

Given the nuance that spencer7593 points out, we want to update all rows where name=DEFAULT_FE_PAGE only if there exists at least one row with name=DEFAULT_FE_PAGE and value=NEW.

We can do this with a multi-table UPDATE:

UPDATE aggregator.argument AS a1
JOIN aggregator.argument AS a2 USING (name)
SET a1.value='AA' 
WHERE a2.name = 'DEFAULT_FE_PAGE' AND a2.value='NEW';

This matches a row a2 with both conditions, then joins that row to all rows a1 that have the same name, even if they have a different value.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bill Karwin

Related Questions