Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    8

    Unanswered: Update different columns in DB2 table based on input data

    I tried to find solution here on dbforum but I couldn't find.

    I need to download data from IMS database (yes, we still use them ) and update DB2 table with those data. Because we have large db, I first create nvsam data file and then I have to update table.

    Those are data I have in data file (I read them sequentially)

    Code:
    ID  TISK    NAK
     1     1    100 
     1     3    200 
     2     1    300
     2     2    400
     3     3    500
     4     1    600
     4     2    700
     4     3    800
    ...

    This is what I have in DB before UPDATE

    Code:
    ID  NAK1   NAK2   NAK3
     1     0      0      0
     2     0      0      0
     3     0      0      0
     4     0      0      0
    ...

    Depending on value in TISK, I have to update NAK1 or NAK2 or NAK3. This is how DB should look after UPDATE

    Code:
    ID  NAK1   NAK2   NAK3
     1   100      0    200
     2   300    400      0 
     3     0      0    500
     4   600    700    800
    ...

    This is PL/I program. It's easy to make 3 procedure for updating different columns and call them based on value of TISK. But I'm wondering is it possible to do UPDATE with CASE in only one? English is not my mother tongue but I hope you understand what I want/need.

    Thank you for the answer.


    Edit:


    DB2 version 10
    ZOS 2.2

    I'm only programmer, that's all I know about version.
    Last edited by Sedma; 01-31-17 at 10:12.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Try this:
    Code:
    update t2 a set
      nak1=coalesce((select b.nak from t1 b where b.id=a.id and b.tisk=1), nak1)
    , nak2=coalesce((select b.nak from t1 b where b.id=a.id and b.tisk=2), nak2)
    , nak3=coalesce((select b.nak from t1 b where b.id=a.id and b.tisk=3), nak3)
    Regards,
    Mark.

  3. #3
    Join Date
    Feb 2012
    Posts
    8
    Quote Originally Posted by mark.bb View Post
    Try this:
    Code:
    update t2 a set
      nak1=coalesce((select b.nak from t1 b where b.id=a.id and b.tisk=1), nak1)
    , nak2=coalesce((select b.nak from t1 b where b.id=a.id and b.tisk=2), nak2)
    , nak3=coalesce((select b.nak from t1 b where b.id=a.id and b.tisk=3), nak3)
    T1 in your example is DB table, but in mine is ordinary NVSAM sequential data set. ID, TISK and NAK are PL variables. Your solution doesn't solve my problem but I'll try it anyway using virtual DB2. I've never seen something like that so I will definitively try it. Thank you.

    For others, bear in mind that I have NVSAM data set + DB2 table, not 2 DB2 tables.

  4. #4
    Join Date
    Feb 2012
    Posts
    8
    Mark, I tried your solution with some modifications. Maybe it's not elegant as I wanted to, but it's much better than to have 3 updates. Beside, it works Thank you so much for an idea.

    Code:
    update t2 set                              
       nak1=coalesce((select :nak from sysibm.sysdummy1 where :tisk = 1), nak1)     
    ,  nak2=coalesce((select :nak from sysibm.sysdummy1 where :tisk = 2), nak2)
    ,  nak3=coalesce((select :nak from sysibm.sysdummy1 where :tisk = 3), nak3)     
    where a.id   = :id;

    In upper solution I always update all 3 columns for every row in data set. Is it possible to update only the column that I need to update? Is there a solution for that?

  5. #5
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Sure, you can construct the query dynamically according to the actual parameter and column values for each row. But this requires one additional select per row to check the actual contents of the interested columns.
    I'm not sure about DB2 for Z, but DB2 for LUW doesn't log updates to a column with the same value. So, there is no need to use different update statements in db2 for LUW to optimize these updates.
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •