Update with a Having clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update with a Having clause

Author

Topic

murph331
Starting Member

2 Posts

Posted - 2009-07-09 : 11:44:00

I'm trying to update some records, but the criteria requires a certain threshold. Can you use a having clause in an update statement? Any feedback is much appreciated. thx

update label
set label = 'By The Numbers'
from label l, t.tran_sum_month
where l.contact_id = t.rep_id
and t.trade_type < 'd'
and t.trade_year > '2007'
and t.firm_id not in ('FHO05028','FHO05948','F 00630','FHO06058','FHO05585','FMB07822','FHO04974')
and t.terr1 <> 'UN'
having (sum(t.trans_value) > '35000.00')

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-09 : 11:59:32

Can't say much without actually seeing your sample data. I'm not even sure of the table names you have used in the query...but this should be a start..

UPDATE l 
SET    label = 'By The Numbers' 
FROM   label l 
       INNER JOIN (SELECT   rep_id, 
                            trade_type, 
                            trade_year, 
                            tran_sum_month, 
                            firm_id, 
                            terr1, 
                            Sum(trans_value) AS trans_value 
                   FROM     tran_sum_month 
                   GROUP BY rep_id, 
                            trade_type, 
                            trade_year, 
                            tran_sum_month, 
                            firm_id, 
                            terr1) t 
         ON l.contact_id = t.rep_id 
WHERE  t.trade_type <> 'd' 
       AND t.trade_year > 2007 
       AND t.firm_id NOT IN ('FHO05028','FHO05948','F 00630','FHO06058', 
                             'FHO05585','FMB07822','FHO04974') 
       AND t.terr1 <> 'UN' 
       AND t.trans_value > 35000

murph331
Starting Member

2 Posts

Posted - 2009-07-09 : 12:08:11

Thanks for the reply, but I'm still looking to update records of people who have done more $35k or more in total sales, not just a trade of $35k or more. Will this query do that? Just want to be 100% sure before I run the query. Thanks again

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-09 : 13:41:00

quote:
Originally posted by murph331

Thanks for the reply, but I'm still looking to update records of people who have done more $35k or more in total sales, not just a trade of $35k or more. Will this query do that? Just want to be 100% sure before I run the query. Thanks again

I have no idea what this means because I dont have access to your data. Please provide some sample data and expected output to get a complete solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources