MySQL

Update Stmt - Update same record multiple times

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2009

Posts: 7

Reputation: nickj is an unknown quantity at this point

Solved Threads: 0

nickj

Offline

Newbie Poster

Update Stmt - Update same record multiple times

Jun 15th, 2009

Hi,

I've having a bit of difficulty with an update statement.

The following select statement returns 34 records...

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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!

•

Join Date: Aug 2008

Posts: 1,160

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Update Stmt - Update same record multiple times

Jun 15th, 2009

•

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?

Custom Application & Software Development
www.houseshark.net

•

Join Date: Jun 2009

Posts: 7

Reputation: nickj is an unknown quantity at this point

Solved Threads: 0

nickj

Offline

Newbie Poster

Re: Update Stmt - Update same record multiple times

Jun 16th, 2009

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?

Last edited by nickj; Jun 16th, 2009 at 3:56 am.

•

Join Date: Aug 2008

Posts: 1,160

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Update Stmt - Update same record multiple times

Jun 16th, 2009

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

Custom Application & Software Development
www.houseshark.net

•

Join Date: Jun 2009

Posts: 7

Reputation: nickj is an unknown quantity at this point

Solved Threads: 0

nickj

Offline

Newbie Poster

Re: Update Stmt - Update same record multiple times

Jun 16th, 2009

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.

•

Join Date: Jun 2009

Posts: 7

Reputation: nickj is an unknown quantity at this point

Solved Threads: 0

nickj

Offline

Newbie Poster

Re: Update Stmt - Update same record multiple times

Jun 17th, 2009

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!