Update SQL based on summed records

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 SQL based on summed records

Author

Topic

DBunn
Starting Member

14 Posts

Posted - 2002-08-13 : 11:22:47

Greetings all,
I have a table that I would like to update based on matching fields to another.

Table1 has fields A1, B1, C1, D1
Table2 has fields A2, B2, C2

I need to update field D1 in Table1 to a value of 'P' if
Table1.A1 = Table2.A2
and Table1.B1 = Table2.B2
and (Table1.C1 = sum of C2 for all records in Table2
where Table1.A1 = Table2.A2
and Table1.B1 = Table2.B2)

I thought this would be simple to put together in one SQL statement (I'm using a DTS SQL execute task), but it has proven to be elusive.

Any help?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-13 : 11:38:12

update Table1
set D1 = 'P'
from Table2
where Table1.A1 = Table2.A2
and Table1.B1 = Table2.B2
and Table1.C1 = (select sum(C2) from Table2 t2
where Table1.A1 = t2.A2
and Table1.B1 = t2.B2)

(and put it in a stored proc).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DBunn
Starting Member

14 Posts

Posted - 2002-08-13 : 12:04:59

Thanks, nr
Worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources