Help with this Complecated Query

Single-click script execution on multiple SQL Servers.

Moderators: JonathanWatts, Anu Deshpande, dlkj, Colin Millerchip, Michelle Taylor

Help with this Complecated Query

Postby vuyiswam » Fri Feb 01, 2008 11:34 am

hi All

i realy need your Help. i have a Table named Property, this Table has a Field named "Lis_key" and Attrib_code and "Func_key", my Table can look like this

Code: Select all
Lis_key ======attrib_code======================  Func_key==

01424545       1212033993                      PV000000
01424545        Null                           GEOSS001
01424545        Null                           GEOSS002
01424545        Null                           GEOSS003

Figuire:1

Now from the Above table, i call records that have Func_key "Parents" and Records that has Func_key "Children". and in my table there are many Parents that have no Children. Am interested in those that have Children. As you can see the Attrib_code of Children is Null, i have this query that Find the Parents that has Children. and its like this



Code: Select all
if Exists(select p1.Property_ID,p1.Lis_key,p1.Func_key,p1.Attrib_code
from sde.Property_Backup p1
where p1.func_key = 'PV000000'
and exists (select 1
      from sde.Property_Backup p2
      where p2.lis_key = p1.lis_key
      and substring(p2.func_key,1,5)='GEOSS' And
      P1.aCTIVE =1))

begin
Update sde.Property_Backup p2
      set p2.Attrib_code = p1.Attrib_code
      where p2.lis_key = p1.lis_key
      and substring(p2.func_key,1,5)='GEOSS'
       And P1.aCTIVE =1
end


Code: Select all
Lis_key ======Attrib_code============Func_key======
===========================================
01424545        1212033993                           PV000000
01424545        1215035993                           PV000000
01424545        3599345445                           PV000000
01424545        5035934544                           PV000000


and now as you above, table this Parents have Children with a Fun_key that is like "GEOSS", and they are null. i want to Copy a Attrib_code of a Parent to a Child as long as the Lis_key as the same. and the Final results should be like this

Code: Select all
Lis_key ======Attrib_code================Func_key======
============================================================
01424545           1212033993                PV000000
01424545           1212033993                GEOSS001
01424545           1212033993                GEOSS002
01424545           1212033993                GEOSS003


No more Nulls for Attrbi_code for GEOSS, So i need an update Statement for the Children.

Thanks

Please Help
vuyiswam
 
Posts: 1
Joined: Fri Feb 01, 2008 11:24 am

Return to SQL Multi Script 1

Who is online

Users browsing this forum: No registered users and 1 guest