Hi,

I've having a bit of difficulty with an update statement. The following select statement returns 34 records...

select b.* from budget as b
inner join ntime t on b.feeearnerkey=t.feeearnerkey
inner join date d on t.datekey=d.datekey
inner join acts a on t.activitykey=a.activitykey
where a.activitycode in (11,12,13,14,15,16,17,18)
and b.fiscalmonth=d.fiscalmonthnum
and b.fiscalyear=d.fiscalyearnum
and b.budgetkey=1
and t.feeearnerkey=520

...if I use a distinct in this query, only 9 rows are returned.

I need to update the budget table for each row that is returned in this query, therefore I will updating the same record multiple times.

I've tried the following two updates, but only 9 records are updated.

update budget as b
inner join ntime t on b.feeearnerkey=t.feeearnerkey
inner join date d on t.datekey=d.datekey
inner join acts a on t.activitykey=a.activitykey
set b.budgetamount=b.budgetamount - (t.minutes / 60)
where a.activitycode in (11,12,13,14,15,16,17,18)
and b.fiscalmonth=d.fiscalmonthnum
and b.fiscalyear=d.fiscalyearnum
and b.budgetkey=1
and t.feeearnerkey=520

update time t
inner join budget b on t.feeearnerkey=b.feeearnerkey
inner join date d on t.datekey=d.datekey
inner join acts a on t.activitykey=a.activitykey
set b.budgetamount=b.budgetamount - (t.minutes / 60)
where a.activitycode in (11,12,13,14,15,16,17,18)
and b.fiscalmonth=d.fiscalmonthnum
and b.fiscalyear=d.fiscalyearnum
and b.budgetkey=1
and t.feeearnerkey=520

The 'budgetamount' field stores an hourly target value for a particular month & year. I need to deduct any minutes (t.minutes / 60) that exist in the 'time' table for the particular month and year.

So although in the budget table, there can only be one entry for a particular month and year, many entries can exist in the time table for that particular month and year.

Maybe I need to apply some kind of aggregation to the time table before deducting from the budgetamount value?

Can anyone help with this query?