Hello Guys,,

This is my first post here...

Actually i like to update a column from table A with a value from table B on some
conditions. The query is as follows:

update krms_Stg.SVHC_RAWTREESTRUCTUREDATAFORALL a
set a.Wt_Fatherid =
(select a1.value
from krms_Stg.RAWTREESTRUCTUREDATAFORALL a1
where a1.NODEID_FATHER=a.NODEID_SON and
a1.TOP_LEVEL_NODE_ID=a.TOP_LEVEL_NODE_ID)
where a.TOP_LEVEL_NODE_ID=a1.TOP_LEVEL_NODE_ID and
a.NODEID_SON=a1.NODEID_SON and
a.NODEID_FATHER=a1.NODEID_FATHER;

But it is throwing an error saying the alias A1 is not recognized.

Please help me out of this..

Thanks for your help in advance,,

Regards,
Magesh.

MERGE statement may help you.
Like this:

merge into krms_Stg.SVHC_RAWTREESTRUCTUREDATAFORALL a
using krms_Stg.RAWTREESTRUCTUREDATAFORALL a1
on a1.NODEID_FATHER = a.NODEID_SON
and a1.TOP_LEVEL_NODE_ID = a.TOP_LEVEL_NODE_ID
when matched then
update set a.Wt_Fatherid = a1.value
;

Thanks tonkuma... But still am having a problem..
Am getting this error..
SQL0788N The same row of target table "KRMS_STG.SVHC_RAWTREESTRUCTUREDATAFORALL" was identified more than once for an update, delete or insert operation of the MERGE statement. SQLSTATE=21506

Doesn't
identify a unique record ????

IIRC, it has to - otherwise you get this message