Red Gate forums :: View topic - Help with this Complecated Query

Go to product support center

SQL Multi Script 1 forum

Help with this Complecated Query

Search in SQL Multi Script 1 forum

Jump to:

Author

Message

vuyiswam

Joined: 01 Feb 2008
Posts: 1

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

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:

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:

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:

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:

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

Display posts from previous:

	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