How to pass values to mySQL nested subquery?

Question

I have a complicated query that boils down to this:

UPDATE usertable
SET userstatus =
    (SELECT numposts*5 as status FROM
        (SELECT count(*) as numposts FROM poststable WHERE poststable.userid = usertable.userid) as table2
    )
WHERE usertable.userid > 0

It's a query that updates every user record and sets the user's status to some calculated value based on the number of rows in a child table.

The problem is that usertable.userid does not make it down to the second level subquery.

The query works when presented like this, with only one level down:

UPDATE usertable
SET userstatus =
    (SELECT count(*) as numposts FROM poststable WHERE poststable.userid = usertable.userid)
WHERE usertable.userid > 0

The problem is that the calculation query in the real situation is very complicated.

The question is: is there a way I can get a 2nd level subquery to recognize a value from the top level query? In this example, is there a way to get usertable.userid recognized 2 levels down?

Thanks! -Nico

looks like a bug, anyway why you cannot use UPDATE usertable SET userstatus = 5* (SELECT count(*) as numposts FROM poststable WHERE poststable.userid = usertable.userid) WHERE usertable.userid > 0 ? — Iłya Bursov
– Iłya Bursov, Commented Oct 21, 2013 at 20:51
You can in this simplified example, but the real situation is far more complicated. I think I can work the real one down to one level, but it gets really awful that way. I was hoping for a better way. — Nico teWinkel
– Nico teWinkel, Commented Oct 21, 2013 at 20:58
In Oracle you would do this by writing a procedure/function that takes in the user id as a param returns the status -- in Oracle that will be very efficient. In MySQL I am not sure on performance. Have you explored writing a MySQL function for this? — David Fleeman
– David Fleeman, Commented Oct 21, 2013 at 21:05
hmm, it looks like mySQL only allows us to correlate subqueries to one level deep :( stackoverflow.com/questions/2435644/… — Nico teWinkel
– Nico teWinkel, Commented Oct 21, 2013 at 21:06
Darn, it turns out I'm not Super enough to create functions, or that would have been a nice and clean solution. We are using a hosted database so I doubt I can get Super status. It works for one row in a stored procedure setting, so I'll keep trying that approach. — Nico teWinkel
– Nico teWinkel, Commented Oct 21, 2013 at 22:41

Bill Karwin · Accepted Answer · 2013-10-21 21:11:05Z

1

Instead of doing a correlated subquery row by row, I would generate a derived table as a one-time subquery for all userid's, then join that to the table you want to update. MySQL supports multi-table update syntax:

UPDATE usertable AS u
LEFT OUTER JOIN (
    SELECT userid, COUNT(*) AS numposts
    FROM poststable
    GROUP BY userid
    ORDER BY NULL
) AS t USING (userid)
SET u.userstatus = 5 * COALESCE(t.numposts, 0)
WHERE u.userid > 0

I know you said your real query is more complex, but the same principle may solve it.

answered Oct 21, 2013 at 21:11

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass values to mySQL nested subquery?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related