db2 - Bulk rows update with select on different tables

Question

I wanted to update a column for multiple rows based on the below condition - I want to update column AUUSRTX of file abhipsha/AUDUSERF only for those rows

WHERE AUDUSERF.AUUSRTP <> 'USR' and AUDUSERF.AUUSRTP <> 'usr'

and the value that needs to be updated have to come from usrprffil.uptext from usrprffil of the matching user id . User ids of both tables are as follows

usrprffil.upuprf = auduserf.auusrid

Can someone please help with a correct query for this requirement.

I wrote the below 3 queries but i am getting error as -"Result of SELECT more than one row" for 1st and 2nd query query 1 -

update  abhipsha/auduserf
set AUUSRTX = (                
    SELECT usrprffil.uptext
    from usrprffil  inner join             
         abhipsha/auduserf                                            
    on upuprf =auusrid  WHERE AUDUSERF.AUUSRTP <> 'USR'            
    and auduserf.auusrtp <> 'usr'   and auusrid = usrprffil.upuprf)

query 2 -

update abhipsha/auduserf set AUUSRTX =                             
   (SELECT uptext fROM usrprffil inner                               
     join abhipsha/auduserf 
    on usrprffil.UPUPRF = auduserf.AUUSRID 
     WHERE
       usrprffil.UPUPRF in (
          SELECT AUUSRID FROM abhipsha/auduserf
          WHERE   
          AUUSRTP <> 'USR' and AUUSRTP <> 'usr'
       ) 
   )

for the 3rd query i am getting - null value error query 3 -

update abhipsha/auduserf 
set  auduserf.AUUSRTX = (
    select UPTEXT      
    from usrprffil   
    WHERE usrprffil.UPUPRF = auduserf.AUUSRID 
    and   usrprffil.UPUPRF    in                                               
       (SELECT AUUSRID FROM abhipsha/auduserf                               
        WHERE AUUSRTP <> 'USR' and   AUUSRTP <> 'usr') 
    and uptext <> ' ' and 
    uptext is not null )

Null values not allowed in column or variable AUUSRTX.

You are saying "file". What Db2 platform and version? – data_henrik Jan 20 '21 at 10:25 — data_henrik, Jan 20 '21 at 10:25

score 0 · Answer 1 · answered Jan 20 '21 at 12:42

Try this:

update  abhipsha/auduserf a
set AUUSRTX =
COALESCE
(
  (                
    SELECT min(u.uptext)
    from usrprffil u
    WHERE a.auusrid = u.upuprf
  )
, AUUSRTX
)  
WHERE a.AUUSRTP NOT IN ('USR', 'usr')

Notes:

The min function is used to not return multiple rows from the sub-select.
If the sub-select returns no rows, the AUUSRTX column value remains as is.

You may adjust the logic above according to your needs, of course.

db2 - Bulk rows update with select on different tables

1 Answers1