SELECT in COALESCE in MySQL

mysql, sql

Solution

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));

Problem

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?

Original source