referring outer derived table from UPDATE command SQL

Question

I need to refer to cartProduct derived table from query in UPDATE command. Can somebody explain me please what I doing wrong and how can I fix it.

UPDATE memberships SET points = (points + COALESCE( 
    (SELECT pts 
    FROM (
        SELECT pid0, SUM (S) as Pts
        FROM ( 
            (SELECT pid0, SUM (pts1) as S
            FROM 
                (SELECT DISTINCT pid0, pid0,pts1
                FROM cartProduct
                WHERE pid0 = i1) as pointsForPath 
            GROUP BY pid0)) as allPtsTbl
        GROUP BY pid0) as temp 
    WHERE memberships.cid = 57010 AND memberships.pid = temp.pid0 ),
0))
FROM (
    SELECT t0.pid as pid0,t1.pid as pid1,t1.invitedby as i1,t1.points as pts1, t2.pid as pid2,t2.invitedby as i2,t2.points as pts2,t3.pid as pid3,t3.invitedby as i3, t3.points as pts3
    FROM memberships t0, memberships  t1, memberships  t2, memberships  t3
    WHERE t0.cid = t1.cid AND t1.cid = t2.cid AND t2.cid = t3.cid) as cartProduct;

It's not really matter what this query have to do cause its a small piece of complicated query that I want to optimize. My only question is how can I refer to cartProduct from UPDATE subquery. The query that I want to optimize is generated by C code... As you can see I have to calculate same things for many times. This query have only 3 derived tables, but it can be 100+ too and then I have to wait for more than 10 hours to calculate it.

UPDATE memberships SET points = points + COALESCE((
SELECT pts 
FROM (
    SELECT pid0, SUM (S) as Pts
    FROM ( 
        SELECT pid0, SUM (pts1) as S
        FROM 
            (SELECT DISTINCT pid0, pid1,pts1
            FROM (
                SELECT t0.pid as pid0, t1.pid as pid1, t1.invitedby as i1, t1.points as pts1 
                FROM memberships t0, memberships t1
                WHERE t0.cid = 57010 AND t1.cid = 57010) as paths
            WHERE pid0 = i1) as pointsForPath
        GROUP BY pid0
        UNION ALL
        SELECT pid0, SUM (pts2) as S
        FROM (
            SELECT DISTINCT pid0, pid2,pts2
            FROM (
                SELECT t0.pid as pid0, t1.pid as pid1, t1.invitedby as i1, t1.points as pts1 , t2.pid as pid2, t2.invitedby as i2, t2.points as pts2 
                FROM memberships t0, memberships t1, memberships t2
                WHERE t0.cid = 57010 AND t1.cid = 57010 AND t2.cid = 57010) as paths
            WHERE pid0 = i1 AND pid1 = i2) as pointsForPath
        GROUP BY pid0
        UNION ALL
        SELECT pid0, SUM (pts3) as S
        FROM (
            SELECT DISTINCT pid0, pid3,pts3
            FROM (
                SELECT t0.pid as pid0, t1.pid as pid1, t1.invitedby as i1, t1.points as pts1 , t2.pid as pid2, t2.invitedby as i2, t2.points as pts2 , t3.pid as pid3, t3.invitedby as i3, t3.points as pts3 
                FROM memberships t0, memberships t1, memberships t2, memberships t3
                WHERE t0.cid = 57010 AND t1.cid = 57010 AND t2.cid = 57010 AND t3.cid = 57010) as paths
            WHERE pid0 = i1 AND pid1 = i2 AND pid2 = i3) as pointsForPath
        GROUP BY pid0 ) as allPtsTbl
GROUP BY pid0) as temp WHERE memberships.cid = 57010 AND memberships.pid = temp.pid0 ),0)

Sorry for my bad english and thanks a lot.

Answer 1

If performance is your problem, you might try:

update memberships
    set points = points + coalesce(s.points, 0)
    from (nasty union all'ed subquery) s
    where memberships.id = s.id

(This assumes that you rename the pid0 field to id.)

That said, you should consider the following:

1. Learn proper join syntax. Doing the joins in the "where" clause is becoming archaic, like using "hath" or "thee" in English. Doing the joins implicitly by selecting particular values (what you are doing) is highly dangerous, since you do not know how many records are being fetched from each table, and a small change to the query could have adverse effects (such as selecting two users rather than one).
2. Put the nasty subquery into a temporary table. Usually, I don't advocate temporary tables, but this may be a case where the optimizer gets confused (this is particularly possible in a query that mixes selects, deletes, and updates).
3. Why are you doing a distinct before the group by. First, you can do "sum(distinct )", although that is almost never necessary. This suggests a problem with your joins. And, bad joins result in queries that don't complete.