CODEWITHSUNDEEP
mysqlsql
user3797053
user3797053

Reputation: 527

Mysql multiple select statements in single query with if condition

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

Upvotes: 1

Views: 374

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

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.

Upvotes: 1

Related Questions