Help with this Complecated Query - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Help with this Complecated Query

Help with this Complecated Query

Options

Previous Topic · Next Topic

vuyiswam

Posted: Friday, February 01, 2008 5:39:28 AM

Rank: Newbie

Joined: 2/1/2008
Posts: 1
Points: 3
Location: South Africa

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

Scott Whigham

Posted: Tuesday, February 05, 2008 3:08:06 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

You lost me with this statement:

Quote:

Now from the Above table, i call records that have Func_key "Parents" and Records that has Func_key "Children".

Users browsing this topic

Guest

Forum Jump

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