SELECT in COALESCE in MySQL
Your problem is presumably that you are getting more than 1 z value returned by the subselects. You can only get one.
You could try:
update xset y = (select z from t where a = b order by (case when a = b and c = d and e = f then 1 when a = b and c = d then 2 else 3 end) limit 1 );However, your specific syntax error is that you need parentheses around the selects. So this is the syntactically correct version:
UPDATE xSET y = COALESCE((SELECT z FROM table WHERE a = b AND c = d AND e = f LIMIT 1), (SELECT z FROM table WHERE a = b AND c = d LIMIT 1), (SELECT z FROM table WHERE a = b LIMIT 1));
It is hard to advise without more details but something like this may work
UPDATE xSET y = COALESCE( ( SELECT z FROM table WHERE a = b AND c = d AND e = f LIMIT 1), ( SELECT z FROM table WHERE a = b AND c = d LIMIT 1 ), ( SELECT z FROM table WHERE a = b LIMIT 1 ));Ensures that each subquery only returns up to 1 row.