• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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
0
sam2929
Asked:
sam2929
  • 2
1 Solution
 
GanparCommented:
May be you should try forign key........

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now