Another Update

[TitleistDBA]

I get the following error on this update.

Error: SQL0811 - Result of SELECT more than one row.

update mvxcjdttst.opricl l
set l.OMFMID = (select s.odfmid
from mvxcjdttst.oprbas s,mvxcjdttst.opricl l
where l.omcono = s.odcono
and l.omitno = s.oditno
and s.odfmid <> ' '
)
Can anybody help with this?

http://www.acushnetcompany.com

 

[ifxtechie]

Hi TitleistDBA,
It is clear that the SELECT inside the UPDATE is retrieving multiple rows.
If that is the case, then you can modify your sql as below:
update mvxcjdttst.opricl l
set l.OMFMID = (select s.odfmid
from mvxcjdttst.oprbas s,mvxcjdttst.opricl l
where l.omcono = s.odcono
and l.omitno = s.oditno
and s.odfmid <> ' ' fetch first 1 rows only
)
Verify the logic of having multiple rows with your SELECT statement.
Hope this helps
Good luck

 

[TitleistDBA]

The problem is there is 467 rows that need to updated for that select statement. I would like to update all of them with one statement. Is that going to be possible?

http://www.acushnetcompany.com

 

[TitleistDBA]

I am making some progress. I changed my update statement to the one belowe. Now I get an error stating the field I am updating does not allow NULLs. so I added and is not null clause but that didn't work!?

update mvxcjdttst.opricl l
set l.OMFMID = (select s.odfmid
from mvxcjdttst.oprbas s
where l.omcono = s.odcono
and l.omitno = s.oditno
and s.odfmid <> ' '
and s.odfmid is not null
)

http://www.acushnetcompany.com

 

[TitleistDBA]

I'm getting closer but now I am back to my first error message. whith this query. It needs to update 467 rows in the opricl table based on that select criteria.


update mvxcjdttst.opricl l
set l.OMFMID = (select s.odfmid
from mvxcjdttst.oprbas s
where l.omcono = s.odcono
and l.omitno = s.oditno
and s.odfmid <> ' ')
WHERE EXISTS (SELECT *
FROM mvxcjdttst.oprbas s
WHERE l.omcono = s.odcono
and l.omitno = s.oditno
and s.odfmid <> ' ')

http://www.acushnetcompany.com

 

[TitleistDBA]

I think I have figured out the problem. I don't have the complete join. That would explain the error.

http://www.acushnetcompany.com

 

[MarcLodge]

Sorry about coming to this late, particularly as you think that you have solved it. It looks that you wanted a correlated update query along the lines of:

UPDATE MVXCJDTTST.OPRICL L
 SET OMFMID = (SELECT S.ODFMID
               FROM MVXCJDTTST.OPRBAS S
               WHERE S.ODCONO = L.OMCONO
               AND   S.ODITNO = L.OMITNO
               AND   S.ODFMID <> ' '
              )

Marc

 

[TitleistDBA]

Marc,
Yes that is what I wanted. The problem I was having is that I was missing part of the key for the join. Thanks for your help.

http://www.acushnetcompany.com