Red Gate forums :: View topic - Help with this Complecated Query
Return to RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Multi Script 1
SQL Multi Script 1 forum

Help with this Complecated Query

Search in SQL Multi Script 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message

Joined: 01 Feb 2008
Posts: 1

PostPosted: Fri Feb 01, 2008 11:34 am    Post subject: Help with this Complecated Query Reply with quote

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


Lis_key ======attrib_code======================  Func_key==

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


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


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))

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


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


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.


Please Help
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group