update script probs again

[sammybee]

Hi All,

Sorry for being a pain, I need to do an update script as below and insert the sum I'm retrieving when I run the script below I get the error group function is not allowed here, any ideas?

Many thanks

Sam

UPDATE sb_void_losses
SET sb_tot_rentloss =SUM (vtr_dr)
WHERE sb_prop_ref IN (
SELECT sb_prop_ref, pro_refno, SUM (vtr_dr)
FROM sb_void_losses, properties, void_transactions
WHERE sb_prop_ref = pro_propref(+)
AND pro_refno = vtr_pro_refno(+)
AND vtr_effective_date BETWEEN sb_void_date AND sb_let_date
GROUP BY sb_prop_ref, pro_refno)

 

[lewisp]

There are three porblems in your SQL.

1) You cant update with a SUM.
2) The column in the SUM is out of context of the UPDATE.
3) Your sub-query returns 3 columns when you have only 1 in the main WHERE.

You will need to update the column using a sub-query which is seperate from the sub-query you are using to determine which rows you want to update.

 

[sammybee]

Hi ,

Sorry can you give me an example of how I would do a seperate sub_query?

Cheers

Sam

 

[lewisp]

Obviously I dont know the structure of your tables, so you will need to sort out the details for yourself:

UPDATE sb_void_losses
SET sb_tot_rentloss = (
         SELECT  SUM (column_name) 
         FROM    table_name
         WHERE   your_where_clause)
WHERE sb_prop_ref IN (SELECT sub-query....)

You can correlate the sub-query back to the table you are updating to restrict the rows used in the SUM.

 

[sammybee]

I'm really stuck here, if I use the query below then it updates all rows with the sum of all records instead of the sum of each individual per sb_prop_ref. Any ideas?

UPDATE sb_void_losses
SET sb_tot_rentloss =
(SELECT SUM (vtr_dr)
FROM sb_void_losses, properties, void_transactions
WHERE sb_prop_ref = pro_propref(+)
AND pro_refno = vtr_pro_refno(+)
AND vtr_effective_date BETWEEN sb_void_date AND sb_let_date)
WHERE sb_prop_ref IN (
SELECT sb_prop_ref
FROM sb_void_losses, properties, void_transactions
WHERE sb_prop_ref = pro_propref(+)
AND pro_refno = vtr_pro_refno(+)
AND vtr_effective_date BETWEEN sb_void_date AND sb_let_date)

 

[lewisp]

Ok, my apologies. I should have been a little more specific. Look at the way the second sub-query is correlated to the table in the UPDATE:

UPDATE sb_void_losses [COLOR=red]svl[/color]
   SET svl.sb_tot_rentloss =
          (SELECT SUM (vtr_dr)
             FROM sb_void_losses, properties, void_transactions
            WHERE sb_prop_ref = pro_propref(+)
              AND pro_refno = vtr_pro_refno(+)
              AND vtr_effective_date BETWEEN sb_void_date AND sb_let_date)
 WHERE svl.sb_prop_ref IN (
          SELECT sb_prop_ref
            [COLOR=red]FROM properties, void_transactions
           WHERE svl.sb_prop_ref[/color] = pro_propref(+)
             AND pro_refno = vtr_pro_refno(+)
             AND vtr_effective_date BETWEEN [COLOR=red]svl.sb_void_date AND svl.sb_let_date)[/color]

The parts in red highlight my changes. You may need to do the same thing in your first sub-query as your SUM values may be wrong.

 

[sammybee]

Hey sorry again,

but this isn't working correctly as it is still updating all with the sum of everything instead of the sum in the first subquery. Shouldn't the group by still be in there somewhere?

Cheers

Sam

 

[Dagon]

sb_void_losses needs to be removed from the "select sum(" bit as well.

 

[sammybee]

HI Dagon,

I'm a still struggling with this, I've left the script running for over an hour with no other use-age. It only needs to update 4000 records, is there any way I can speed this up?

Cheers

Sam