update stmt

Posted on 2012-09-06
Last Modified: 2012-09-17
I have two tables

id    key  start_dte           end_dt      camp_id
123   104  Aug-10-2012       dec-30-3030     102
123   104  Aug-10-2012       dec-30-3030     103
123   104  Aug-10-2012       dec-30-3030     104

id_bb    key_bb  start_dte           end_dt      identy_key flag
123      104     Aug-10-2012       sep-6-2012     234         N
234      104     sep-7-2012       dec-30-3030                 y  

we need to update Bridge_aa id and end_dt based upon the identy_key (234) as this is new id_bb row going forward as 123 has expired

so final look after update should be

id    key  start_dte           end_dt      camp_id
234   104  Aug-10-2012       sep-6-2012     102
234   104  Aug-10-2012       sep-6-2012     103
234   104  Aug-10-2012       sep-6-2012     104
Question by:sam2929
    LVL 2

    Expert Comment

    May be you should try forign key........
    LVL 37

    Accepted Solution

    assuming the key is actually composed from key and camp id, you nee something like

    update bridge_aa tt
    set id = (select t2.id_bb from dim_bb t1 join dim_bbt2 on t1.identity_key = t2.id_bb where t1.id_bb = tt.id_bb),
    end_dt = (select t2.end_dt from dim_bb t1 join dim_bbt2 on t1.identity_key = t2.id_bb where t1.id_bb = tt.id_bb)

    Author Comment

    problem in this update i get below error
    but t2.id_bb and set id both are not null but identity_key is not null is that causing the issue?

    SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=3, TABLEID=538, COLNO=8                                     " is not allowed.


    One of the following occurred:
    LVL 37

    Expert Comment

    null means the subquery did not return a match so there is something wrong with your data

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now