Need Help on this followng SQl Problem[resolved]

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:

---

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)

---

Code:

---

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

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

Maybe something like this:

Code:

---

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.

hi Man thanks for your Help.

i have used a Cursor to Achive this, like this

Code:

---

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