Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Need help with SQL

    Hi All,

    Having following two tables as

    Code:
    C:\Documents and Settings\db2admin>db2 select * from cl
    
    ACC_NUM       AMT
    ------------- -----------
    AB112                  27
    AB113                  47
    AB114                  87
    AB115                  37
    AB116                  97
    AB117                  67
    AB118                  87
    AB119                  77
    
      8 record(s) selected.
    
    C:\Documents and Settings\db2admin>db2 select * from tm
    
    ACC_NUM       AMT
    ------------- -----------
    AB119                  70
    AB116                  40
    AB113                  20
    
      3 record(s) selected.
    All columns defined as NOT NULL

    SQL to update returns an Error

    Code:
    C:\Documents and Settings\db2admin>db2 update cl source set amt =
     select amt from tm target where source.acc_num=target.acc_num)
    
    DB21034E  The command was processed as an SQL statement because
     it was not avalid Command Line Processor command.  During SQL 
    processing it returned:SQL0407N  Assignment of a NULL value to 
    a NOT NULL column "TBSPACEID=4,TABLEID=530, COLNO=1" is 
    not allowed.  SQLSTATE=23502
    I undestand that the way I write statement is returning nulls.

    Code:
    UPDATE CL SOURCE SET
            AMT = (
            
                     SELECT AMT FROM TM TARGET
                     WHERE SOURCE.ACC_NUM=TARGET.ACC_NUM
            
                      );
    Please help me write correct update statement : Need to update table CL from table TM simply to set AMT as in TM table where ACC_NUM matches.

    Thanks
    DBFinder

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Add
    Code:
     WHERE EXISTS (
                     SELECT AMT FROM TM TARGET
                     WHERE SOURCE.ACC_NUM=TARGET.ACC_NUM
                  )
    Or, use MERGE statement.

    http://www.dbforums.com/db2/1640241-...query-db2.html

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks,

    Just before I got feedback yesterday,I used following

    UPDATE CL SOURCE SET
    AMT = (

    SELECT AMT FROM TM TARGET
    WHERE SOURCE.ACC_NUM=TARGET.ACC_NUM

    )

    where acc_num in (select acc_num from TM) ;
    Which resolved the problem.

    Thanks for your help. This makes makes me more confident.

    DBFinder

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MERGE will be simpler by removeng redundant sub-query.
    Code:
    MERGE INTO
          cl target
    USING tm source
      ON  source.acc_num = target.acc_num
    WHEN MATCHED THEN
          UPDATE
          SET amt = source.amt
    ;
    I prefer to use "target" for updating table.
    Last edited by tonkuma; 03-31-09 at 16:12.

  5. #5
    Join Date
    Mar 2009
    Posts
    5
    I prefer MERGE in this situation.

Posting Permissions

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