How do i rewrite this query? - 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 2008 Forums

Transact-SQL (2008)

How do i rewrite this query?

Author

Topic

PGG_CA
Starting Member

24 Posts

Posted - 2012-06-14 : 11:39:39

I have these tables and fields:

TableA
fields: id, totaltime

TableB
fields: id, time_min

Sample Data:

TableA
100, NULL
101, NULL
102, NULL

TableB
100, 5
100, 10
101, 12
101, 4
101, 6
102, 2

I have this query that would update the totaltime field in TableA with a sum of the time_min for each ID. TableA, after the update query, should now look like this:

TableA
100, 15
101, 22
102, 2

UPDATE a
SET a.totaltime = (SELECT SUM(TableB.time_min) FROM TableB inner join
TableA on TableB.id = TableA.id GROUP BY TableA.id)
FROM TableA a inner join TableB b on a.id=b.id

When I execute it, I get the error: Subquery returned more than 1 value. This not permitted when the subquery follows =, ... or when the subquery i used as an expression.

How do I accomplish what I want to do?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 12:00:49

update tableA
set totaltime = (select sum(time_min) from TableB b where a.id = b.id)
from TableA a

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-14 : 12:01:20

malformed. There are many different ways.

Here using a derived table


UPDATE a SET
	[totalTime] = sumb.[total]
FROM
	TableA AS a
	JOIN (
		SELECT
			b.[id] AS [id]
			, SUM(b.[Time_min]) AS [total]
		FROM
			TableB AS b
		GROUP BY
			b.[id]
		)
		AS sumb ON sumb.[id] = a.[ID]

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources