Ad:

MySQL Discussion Thread
Unsolved
Views: 1333

Jun 15th, 2009

Update Stmt - Update same record multiple times

Expand Post »

Hi,

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

The following select statement returns 34 records...

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.

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!

Similar Threads

How to Update a record in a database in PHP
update record error-mysql_numrows(): supplied argument is not a valid MySQL in PHP
update a record on load in Oracle
vb6 update record in the database in Visual Basic 4 / 5 / 6
Update record Problem. in ASP.NET

nickj

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Jun 2009

Permalink

Jun 15th, 2009

Re: Update Stmt - Update same record multiple times

Quote ...

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?

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Jun 16th, 2009

Re: Update Stmt - Update same record multiple times

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.

nickj

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Jun 2009

Permalink

Jun 16th, 2009

Re: Update Stmt - Update same record multiple times

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Jun 16th, 2009

Re: Update Stmt - Update same record multiple times

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Jun 2009

Permalink

Jun 17th, 2009

Re: Update Stmt - Update same record multiple times

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!

nickj

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Jun 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Can Crystal Reports be used with MySQL?

Next Thread in MySQL Forum Timeline: Supplied argument is not a valid MySQL result resource

DaniWeb Message

Cancel Changes

User Name
Password