Issue with Update Set - 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)

Issue with Update Set

Author

Topic

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-18 : 09:38:16

Not sure how to resolve the issue.
Receiving error:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Table1
id seq amt ben
1 1 100 5dd10
1 2 100 5dd10
1 3 100 5dd10

Table2
ben Netw type
5dd10 O ded

table3
1 300

UPDATE table3
SET Amt=
(SELECT SUM(Amt)
FROM table1 t1
INNER JOIN table2 t2 ON t1.Ben = t2.Ben
WHERE table3.ID = t1.ID AND (t2.NetW = 'O') AND (t2.Type = 'DED')
GROUP BY t1.ID,t1.Ben
HAVING SUM(AMT) > 0)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 09:40:03

quote:
Originally posted by dowens

Not sure how to resolve the issue.
Receiving error:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Table1
id seq amt ben
1 1 100 5dd10
1 2 100 5dd10
1 3 100 5dd10

Table2
ben Netw type
5dd10 O ded

table3
1 300

UPDATE table3
SET Amt=
(SELECT Coalesce(SUM(Amt), 0)
FROM table1 t1
INNER JOIN table2 t2 ON t1.Ben = t2.Ben
WHERE table3.ID = t1.ID AND (t2.NetW = 'O') AND (t2.Type = 'DED')
~~GROUP BY t1.ID,t1.Ben~~
HAVING SUM(AMT) > 0)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-04-18 : 09:43:43

THAT WORKED THANK YOU!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources