Update Stmt - Update same record multiple times

[nickj]

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 add a distinct clause to the statement, only 9 records are returned from the budget table.

I'm attempting to update the budget table 34 times, which means updating the same record from the budget table multiple times in the same update statement.

I've tried variations of the update statement, but only 9 records are ever 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

Can anyone give any advice on how to go about updating the 34 records in one update statement.

Many Thanks!
:)

[dickersonka]

If I add a distinct clause to the statement, only 9 records are returned from the budget table.

are you sure you are needing to do 34 updates, 9 records only need 9 updates

if the update isn't working properly, can you give us a little more of the schema and also what the distinct column is?

[nickj]

yes, I definately need to update the budget table 34 times.

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?

[dickersonka]

ahhh, so for every record that exists in acts you are needing an update

my advice would be to use a sql 'for each' loop, you can use a cursor and iterate through each row and execute the update statement in there

[nickj]

not acts, but for every record that exists in 'time' that has a certain code in act, update the corresponding budget record.

wanted to avoid cursors but looks like this is the only option.

cheers.

[nickj]

I think I can achieve what I want with a update statement and an aggregate function in a correlated subquery.

...so don't need to go down the route of cursors / SP's, which is a good job as the version i'm working on doesn't support SP's!

:icon_cool: