How can I get a sum in the where clause of an update? 2

[florens]

I have a table: contract. Each contract has a total number of hours. When work is done within a contract, a user registers the number of hours he/she worked. This happens in the tabel: register.

I want to send a daily e-mail to management containing all contracts which are low on available hours. I use something like the following select query:

Select
(contract.hours - sum(register.registeredhours)) as available_hours
From
contract
inner join register on contract_id = register_contract_id

This works fine, but now I want to set a mailed field to '1' so that the same contract isn't mailed twice or more.

So I wanted an update something like this:

update
contract
set
contract_mailed = '1'

And now I'm stuck. :-( I've tried to use inner join and having clauses, where clauses, even if (but it gives an error because more than one records are returned). And I can't get the query to only update the records which are low on available hours.

I hope I made my problem clear, and that someone can help me out.
Thanks

 

[johnherman]

How about something like this (idea only - untested code):

update
contract
set
contract_mailed = '1'
where 40 <

(Select
(contract.hours - sum(register.registeredhours)) as available_hours
From
contract
inner join register on contract_id = register_contract_id)


Replace <40> with your hours threshhold. Or maybe use a percent, say contract.hours/10



-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[florens]

Thanks for your reply. I've tried this, but since the select statement finds more than one record I get an error that it's not possible to have a subquery returning more than one error.

 

[jbenson001]

Do what johnherman is suggesting but in 2 steps
First

Select
(contract.hours - sum(register.registeredhours)) as available_hours
From
contract
inner join register on contract_id = register_contract_id

into a temp table

then join back to the table and update where the rows meet what ever condition you specify

 

[maswien]

try this:

update
contract
set
contract_mailed = '1'
where contract_id in 
 ( select contract_id
   from contract inner join register
        on contract_id = register_contract_id
   group by contract_id
   having max(contract.hours) - sum(register.registeredhours) < @somevalue
   )

 

[florens]

Thank you jbenson001 and maswien. A combination of your suggestions let to the answer. I now insert my id's into a temp table, then use something like maswiens update code to update my contract table and after that I empty the temp table. Works like a charm, a slow charm, but a charm it is

*Slow is not a problem, it only happens once a day and that person knows he has to wait about 10-15 seconds, so no problem at all.