Need Help on this followng SQl Problem[resolved]

[vuyiswamb]

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

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

select p1.Attrib_code,p1.Lis_key
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)

Lis_key ======Attrib_code============Func_key======
===========================================
01424545        1212033993         PV000000
01424545        1215035993         PV000000
01424545        3599345445         PV000000
01424545        5035934544         PV000000

Figuire:2



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.

Thanks

Please Help

[szlamany]

Maybe something like this:

Update sde.property_backup Set Attrib_Code=(Select Top 1 P2.Attrib_Code
                          From sde.property_backup p2
                          Where p2.Lis_Key=p1.Lis_Key and p2.Attrib_Code is not null)
    From sde.property_backup p1
    Where p1.Attrib_Code is null

The WHERE clause of the main UPDATE query only updates those rows with null Attrib_Code values.

And the update value comes from a sub-query - that is related to the row by a matching Lis_Key and also doesn't have a null Attrib_Code.

There is a danger that the sub-query will return a null - if no row exists for that Lis_key with a Attrib_code value. But that null will simply update into an already existing null value anyway.

[vuyiswamb]

hi Man thanks for your Help.

i have used a Cursor to Achive this, like this

DECLARE		@CUR_LIS_KEY VARCHAR(20),
	             @CUR_ATTRIB_CODE VARCHAR(12)

DECLARE PARENT_CURSOR CURSOR FOR
	SELECT	 LIS_KEY, ATTRIB_CODE
	FROM	SDE.PROPERTY P1
	WHERE	EXISTS(SELECT 1 
		FROM SDE.PROPERTY_Summary P2 
		WHERE P2.FUNC_KEY LIKE 'GEOSS%' AND
		P2.LIS_KEY = P1.LIS_KEY And p2.Attrib_code is Null
	             And p2.Archive_Date is Null                            
)		

OPEN PARENT_CURSOR
	FETCH	NEXT FROM PARENT_CURSOR
	INTO	@CUR_LIS_KEY, @CUR_ATTRIB_CODE

WHILE @@FETCH_STATUS = 0
BEGIN
	UPDATE	SDE.PROPERTY
	SET		ATTRIB_CODE = @CUR_ATTRIB_CODE
	WHERE	FUNC_KEY LIKE 'GEOSS%' AND
			LIS_KEY = @CUR_LIS_KEY
	
	FETCH	NEXT FROM PARENT_CURSOR
	INTO	@CUR_LIS_KEY, @CUR_ATTRIB_CODE
END

DEALLOCATE PARENT_CURSOR

Thanks