Toad World® Forums

DB2 SQL update all returned records

I’m trying to update ALL records returned from a subselect as below example. How can i do? Currently is errors saying more than one result is not allowed.

thanks.

update FILE set (o1eutp , o1sgrp , o1sls# ) =
(select
f4 , f6 , f5
from OTHERFILE x join FILE y on x.f2 = char(y.o1cst1) and x.f1 = char(y.o1ord# ) ) ;

I assume that you are getting a DB2 error stating that more than one row is being returned and the statement fails. It appears that you are running DB2 for iSeries based on your query. I know that for DB2 on LUW you would have to code an application or procedure to declare a cursor for the select statement and then on the update statement’s where clause use the CURRENT OF cursor clause. I would recommend you look up the IBM DB2 iSeries reference guide for further information.