update stmt

Hi,
I have two tables

Bridge_AA
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

dim_bb
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

Bridge_AA
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
sam2929Asked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
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)
0
 
GanparCommented:
May be you should try forign key........

http://www.w3schools.com/sql/sql_foreignkey.asp
0
 
sam2929Author Commented:
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.

Explanation:

One of the following occurred:
0
 
momi_sabagCommented:
null means the subquery did not return a match so there is something wrong with your data
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.