Complex update statement - 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)

Complex update statement

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2009-02-17 : 13:41:17

For me this is complex and I am having trouble trying to figure it out. I need to update a field in table1 using data from table1 and another table. When I run my query I get "Subquery returned more than 1 value. This is not permitted........... I knew when I wrote this I was going to have problems but I just can't figure out how to fix it.

UPDATE TABLE1
SET Expense =
(SELECT CASE WHEN 'b.state' = 'c.state' THEN CityVoice_intra * (CallDurationSec / 60) ELSE CityVoice_inter * (CallDurationSec / 60)
END AS Expense
FROM TABLE1 AS a LEFT OUTER JOIN
LergData AS b ON 'b.npanxx' = LEFT('a.OrigNumber', 6) LEFT OUTER JOIN
LergData AS c ON 'c.npanxx' = LEFT('a.TermNumber', 6) LEFT OUTER JOIN
CityVoice ON LEFT(a.TermNumber, 6) = CityVoice.CityVoice_npanxx
WHERE (a.Vendor = 'V-CITYVOICE-GW1'))

The select statement work perfect. Now I just want to update the Expense field with the output for each record.

Thanks,
Nick

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 13:44:36

quote:
Originally posted by nhess80

For me this is complex and I am having trouble trying to figure it out. I need to update a field in table1 using data from table1 and another table. When I run my query I get "Subquery returned more than 1 value. This is not permitted........... I knew when I wrote this I was going to have problems but I just can't figure out how to fix it.

UPDATE TABLE1
SET Expense =
(SELECT CASE WHEN 'b.state' = 'c.state' THEN CityVoice_intra * (CallDurationSec / 60) ELSE CityVoice_inter * (CallDurationSec / 60)
END AS Expense
FROM TABLE1 AS a LEFT OUTER JOIN
LergData AS b ON 'b.npanxx' = LEFT('a.OrigNumber', 6) LEFT OUTER JOIN
LergData AS c ON 'c.npanxx' = LEFT('a.TermNumber', 6) LEFT OUTER JOIN
CityVoice ON LEFT(a.TermNumber, 6) = CityVoice.CityVoice_npanxx
WHERE (a.Vendor = 'V-CITYVOICE-GW1'))

The select statement work perfect. Now I just want to update the Expense field with the output for each record.

Thanks,
Nick

May be this,

UPDATE TABLE1
SET Expense =
CASE WHEN b.state = c.state THEN CityVoice_intra * (CallDurationSec / 60) ELSE CityVoice_inter * (CallDurationSec / 60) END  
FROM TABLE1 AS a LEFT OUTER JOIN
LergData AS b ON b.npanxx = LEFT(a.OrigNumber, 6) LEFT OUTER JOIN
LergData AS c ON c.npanxx = LEFT(a.TermNumber, 6) LEFT OUTER JOIN
CityVoice ON LEFT(a.TermNumber, 6) = CityVoice.CityVoice_npanxx
WHERE (a.Vendor = 'V-CITYVOICE-GW1')

nhess80
Yak Posting Veteran

83 Posts

Posted - 2009-02-17 : 13:51:14

Wow thank you for the quick response that worked perfect...

Thanks,
Nick

quote:
Originally posted by sakets_2000

quote:
Originally posted by nhess80

For me this is complex and I am having trouble trying to figure it out. I need to update a field in table1 using data from table1 and another table. When I run my query I get "Subquery returned more than 1 value. This is not permitted........... I knew when I wrote this I was going to have problems but I just can't figure out how to fix it.

UPDATE TABLE1
SET Expense =
(SELECT CASE WHEN 'b.state' = 'c.state' THEN CityVoice_intra * (CallDurationSec / 60) ELSE CityVoice_inter * (CallDurationSec / 60)
END AS Expense
FROM TABLE1 AS a LEFT OUTER JOIN
LergData AS b ON 'b.npanxx' = LEFT('a.OrigNumber', 6) LEFT OUTER JOIN
LergData AS c ON 'c.npanxx' = LEFT('a.TermNumber', 6) LEFT OUTER JOIN
CityVoice ON LEFT(a.TermNumber, 6) = CityVoice.CityVoice_npanxx
WHERE (a.Vendor = 'V-CITYVOICE-GW1'))

The select statement work perfect. Now I just want to update the Expense field with the output for each record.

Thanks,
Nick

May be this,

UPDATE TABLE1
SET Expense =
CASE WHEN b.state = c.state THEN CityVoice_intra * (CallDurationSec / 60) ELSE CityVoice_inter * (CallDurationSec / 60) END
FROM TABLE1 AS a LEFT OUTER JOIN
LergData AS b ON b.npanxx = LEFT(a.OrigNumber, 6) LEFT OUTER JOIN
LergData AS c ON c.npanxx = LEFT(a.TermNumber, 6) LEFT OUTER JOIN
CityVoice ON LEFT(a.TermNumber, 6) = CityVoice.CityVoice_npanxx
WHERE (a.Vendor = 'V-CITYVOICE-GW1')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources