Change select to update - 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 2005 Forums

Transact-SQL (2005)

Change select to update

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-05 : 14:31:13

This select statement gives me totals I'm looking for. Now I want to take those totals and update a different table.


select cus_no,sum(tot_sls_amt)as total_sales
from oehdrhst_sql
where year(inv_dt)=year(getdate()) and month(inv_dt)=month(getdate()) and oehdrhst_sql.orig_ord_type <> 'C'
group by cus_no

I want to update Userfield1 in the arcusfil. arcusfil will link to oehdrhst by cus_no

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-05 : 14:34:16

Try this:

UPDATE a
SET Userfield = sum(o.tot_sls_amt)
FROM arcusfil a
JOIN oehdrhst_sql o
ON a.cus_no = o.cus_no
WHERE year(o.inv_dt)=year(getdate()) and month(o.inv_dt)=month(getdate()) and o.orig_ord_type <> 'C'
GROUP BY o.cus_no

Please test it first. Maybe put it into a transaction so that you can rollback if needed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-05 : 14:38:02

Doesn't seem to like where the group by is.

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'group'.

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 15:22:36

Is this the same as this one?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151112

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources