SELECT in COALESCE in MySQL

Question

I'm trying to do the following in MySQL:

UPDATE
    x
SET
    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 sounds very valid to me. I'm trying to update a column with the best suitable value. If I can find a record matching the 3 criteria -> that's the one I need. Else the one suiting 2 criteria, else a record matching just one criterion.

I can do this in 3 update queries, but I don't understand why this isn't working. According to the manual:

COALESCE returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

That's exactly what I need. but it gives the following error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT'

Am I missing something here?

Answer 1

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 x
set 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
    x
SET
    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));

Answer 2

It is hard to advise without more details but something like this may work

UPDATE
    x
SET 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.