CODEWITHSUNDEEP
mysqlsubquery
Nico teWinkel
Nico teWinkel

Reputation: 880

How to pass values to mySQL nested subquery?

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

Upvotes: 0

Views: 2133

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562791

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.

Upvotes: 1

Related Questions