Update - 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

SQL Server Development (2000)

Update

Author

Topic

Mattn
Starting Member

2 Posts

Posted - 2007-03-02 : 18:14:57

Hi there

I am trying to update a table with information from a second table and it keeps crashing

Update mvxcdta012.oohead00 set mvxcdta012.oohead00.oafre1=(Select mvxcdta012.ocusma00.okcscd where mvxcdta012.ocusma00.okpyno=mvxcdta012.oohead00.oapyno)
where exists(Select mvxcdta012.ocusma00.okcscd where mvxcdta012.ocusma00.okpyno=mvxcdta012.oohead00.oapyno)

I received this error

Error: SQL0104 - Token MVXCDTA012 was not valid. Valid tokens: , FROM INTO. (State:37000, Native Code: FFFFFF98)

I then thought that this may work.

Update mvxcdta012.oohead00 set mvxcdta012.oohead00.oafre1=(Select mvxcdta012.ocusma00.okcscd from mvxcdta012.ocusma00 inner join mvxcdta012.oohead on mvxcdta012.ocusma00.okpyno=mvxcdta012.oohead00.oapyno)
where exists(Select mvxcdta012.ocusma00.okcscd from mvxcdta012.ocusma00 inner join mvxcdta012.oohead on mvxcdta012.ocusma00.okpyno=mvxcdta012.oohead00.oapyno)

The new error

Error: SQL0901 - SQL system error. (State:S1000, Native Code: FFFFFC7B)

Last I ask someone to look at the code they said try this

update #tmp_prueba set oafre1= (select ok.okcscd from espejo_v12.dbo.ocusma ok
inner join espejo_v12.dbo.oohead a on ok.okpyno=a.oapyno) where exists (Select okcscd from espejo_v12.dbo.ocusma B where b.okpyno=#tmp_prueba.oapyno)

Last error

by show a error " Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."

matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 18:34:26

This returns more than 1 value
(select ok.okcscd from espejo_v12.dbo.ocusma ok
inner join espejo_v12.dbo.oohead a on ok.okpyno=a.oapyno)

replace with

(select top 1 ok.okcscd from espejo_v12.dbo.ocusma ok
inner join espejo_v12.dbo.oohead a on ok.okpyno=a.oapyno)

I deliberately put no order by! Since OP does not know how to handle this, I assume he does not know why the query returns 2 or more values and hence does not know which of the values to return.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources