complex update statement

[dp]

Hi guys,

I have inherited this table that has very bad relationship conventions. the
parent and child are in the same table:

Customer Table structure:
ListID(PK), FullName(parent fullname),ParentID(FK),ParentFullName(child
fullname),sublevel(parent=0,child > 0)

example:

ListID Name FullName ParentRef_ListID ParentRef_FullName Sublevel
780002-1197721014, Smashing Pumpkins , Smashing Pumpkins,0
790004-1197728319, Sublevel 1, Smashing Pumpkins :Sublevel
1 ,780002-1197721014 Smashing 1
7A0005-1197728414 Sublevel 2 Smashing Pumpkins :Sublevel 1:Sublevel
2 790004-1197728319 Smashing Pumpkins :Sublevel 1 2





So, I am trying to create an UPDATE statement that will rename the children
"fullName" field when the parent fullname gets changed
I have tried the following SQL statement, but it only returns the second
sublevel. If I try to include other levels I get "field name is duplicated
error"

UPDATE Customer as c LEFT JOIN Customer as sub ON c.ListID =
sub.ParentRef_ListID
SET sub.FullName = Replace(sub.FullName,'Smashing Pumpkins', 'newvalue' ),
sub.ParentRef_FullName = Replace(sub.ParentRef_FullName, 'Smashing
Pumpkins','newvalue' )
WHERE c.ParentRef_ListID ='780002-1197721014'

sorry, if I am not being clearer, I am a bit confused myself. But, I must
use these tables.

Thanks in advanced for any help,

dp