Updating table from another table

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

General SQL Server Forums

New to SQL Server Programming

Updating table from another table

Author

Topic

ilanshl
Starting Member

1 Post

Posted - 2013-09-02 : 04:51:43

Hi,
Could you please advise what is the problem with this code

UPDATE FS_ALERT
SET FS_ALERT.REMAINING_DELTA =
(
select
FS_FULL_STATUS_VIEW.REMAINING_DELTA
from
FS_FULL_STATUS_VIEW,FS_ALERT
where
FS_ALERT.REMAINING_DELTA <0 AND FS_FULL_STATUS_VIEW.REMAINING_DELTA <0
)

WHERE EXISTS
(
select
FS_FULL_STATUS_VIEW.REMAINING_DELTA
from
FS_FULL_STATUS_VIEW,FS_ALERT
where
FS_FULL_STATUS_VIEW.FS_SERVER=FS_ALERT.FS_SERVER

)

the error I'm getting:
ORA-01427: single-row subquery returns more than one row

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 04:59:25

Try this:

UPDATE FS_ALERT
SET FS_ALERT.REMAINING_DELTA = 
(
select 
FS_FULL_STATUS_VIEW.REMAINING_DELTA 
from FS_FULL_STATUS_VIEW
where FS_FULL_STATUS_VIEW.FS_SERVER=FS_ALERT.FS_SERVER 
      AND FS_ALERT.REMAINING_DELTA <0 
      AND FS_FULL_STATUS_VIEW.REMAINING_DELTA <0
)

Refer this link: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fsqlp%2Frbafyexsub4.htm

Any way this is not the Oracle forum.. Its better to post relevant forums like dbforums.com for quick responses

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-09-04 : 05:58:33

TRY THIS :

UPDATE FS_ALERT
SET FS_ALERT.REMAINING_DELTA = FS_FULL_STATUS_VIEW.REMAINING_DELTA
FROM FS_FULL_STATUS_VIEW V
INNER JOIN FS_FULL_STATUS_VIEW VV
ON V.REMAINING_DELTAID = VV.REMAINING_DELTAID
WHERE FS_FULL_STATUS_VIEW.FS_SERVER=FS_ALERT.FS_SERVER
AND FS_ALERT.REMAINING_DELTA <0
AND FS_FULL_STATUS_VIEW.REMAINING_DELTA <0

P.V.P.MOhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources