Geek asks and answers

SELECT in COALESCE in MySQL

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

That sounds very good to me. I am trying to update a column with the best fit value . If I can find an entry that meets three criteria → the one I need. One more suitable 2 criteria, otherwise the record meets only one criterion.

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

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

This is 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'

Did I miss something?

+5
source share
2 answers

, , 1 z, . .

:

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

, . , :

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

- , -

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

, 1 .

+2

More articles:


All Articles