Mysql multiple select statements in single query with if condition

Question

i have table users with id,name,type,active,...

i have another table orders with orderid,userid,...

i want to update orders table in such a way that

UPDATE orders SET userid=(SELECT id FROM users WHERE type="some" and active=1)

but my problem is if SELECT id FROM users WHERE type="some" and active=1 doesnt have any result i want to use SELECT id FROM users WHERE type="some" limit 0,1

ie the first result

i can do this easly in any language like php/python etc but i just have access to mysql server so cannot do that

but how can i do in pure sql in single query

i tried if statement but not working

SELECT id FROM users WHERE type="some" order by active <> 1 limit 1 — juergen d
– juergen d, Commented Nov 4, 2019 at 12:36

Gordon Linoff · Accepted Answer · 2019-11-04 12:36:08Z

Here is one method using ORDER BY:

UPDATE orders o
    SET userid = (SELECT u.id
                  FROM users u
                  WHERE u.type = 'some'
                  ORDER BY active DESC
                  LIMIT 1
                 );

This assumes that active only takes on the values 0 and 1. If there are other values, use ORDER BY (active = 1) DESC.

Performance should be fine with an index on users(type, active, id).

Another method uses aggregation and COALESCE():

UPDATE orders o
    SET userid = (SELECT COALESCE(MAX(CASE WHEN active = 1 THEN u.id END),
                                  MAX(u.id)
                                 )
                  FROM users u
                  WHERE u.type = 'some'
                 );

I would expect the ORDER BY to be a wee bit faster, but sometimes MySQL surprises me with aggregations in correlated subqueries. That said, if you have very few rows for a given type, the performance difference may not be noticeable.

Collectives™ on Stack Overflow

Mysql multiple select statements in single query with if condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related