Aggregate to 'plain' query

Question

I have a query which uses aggregate functions to assign the maximum absolute of the values to another column in the table. The problem is that it takes whole lot of time (apprx. adds upto 10-15 seconds) to query completion time. This is what the query looks like:

UPDATE calculated_table c 
    SET tp = (SELECT MAX(ABS(s.tp)) 
                FROM ts s INNER JOIN tc t ON s.id = t.id
                GROUP BY s.id);

Where id is not unique, hence the grouping. tp is a numeric whole number field. Here is what the tables look like:

TABLE ts
       PID(primary)       |            id (FKEY)             |                      tp (integer)                   
--------------------+-----------------------------+------------------------------------------------------
 1                        | 2                                | -100
 2                        | 2                                | -500
 3                        | 2                                | -1000

TABLE tc
       PID(primary)       |            id (FKEY)                              
--------------------+-----------------------------+-------------------------
 1                        | 2                               

I want the output to look like:

TABLE c
       PID(primary)       |         tp (integer)                   
--------------------+-----------------------------+--------
 1                        | 1000                                

I tried to make it work like this:

UPDATE calculated_table c 
    SET tp = (SELECT s.tp
                FROM ts s INNER JOIN tc t ON s.id = t.id
                ORDER BY s.tp DESC
                LIMIT 1);

Though it improved the performance, however the results are incorrect.. any help would be appreciated?

Answer 1

I did manage to modify the query, turnsout nesting aggregate functions is not a good option. However, if it helps anyone, here is what I ended up doing:

UPDATE calculated_table c 
    SET tp = (SELECT ABS(s.trade_position)
                FROM ts s INNER JOIN tc t ON s.id = t.id
                WHERE c.id = s.id
                ORDER BY ABS(s.tp) DESC
                LIMIT 1);