urgent - complex Update

[mcl]

Hi,

How can write this Sybase statement in Oracle (8.1.7)?

mcl

update ITX_APPLICATION_PATH set
APPLICATION_ID = (select APPLICATION_ID from ITX_APPLICATION where APPLICATION_NAME = B.APPLICATION_NAME),
PATH_TYPE = B.PATH_TYPE,
PHYSICAL_PATH = B.PHYSICAL_PATH,
DESCRIPTION = B.DESCRIPTION,
ITX_VERSION = ITX_VERSION+1,
ITX_MODULE = B.ITX_MODULE
from ITX_APPLICATION_PATH A, VIP_APPLICATION_PATH B
where A.PATH_NAME = B.PATH_NAME;

[ales]

Hi, try this:

update ITX_APPLICATION_PATH
set
APPLICATION_ID = (select APPLICATION_ID from ITX_APPLICATION where APPLICATION_NAME = B.APPLICATION_NAME),
ITX_VERSION = ITX_VERSION+1,
(PATH_TYPE, PHYSICAL_PATH, DESCRIPTION, ITX_MODULE) =
(select B.PATH_TYPE, B.PHYSICAL_PATH, B.DESCRIPTION, B.ITX_MODULE
from VIP_APPLICATION_PATH B
where ITX_APPLICATION_PATH.PATH_NAME = B.PATH_NAME
)
where ITX_APPLICATION_PATH.PATH_NAME in (select distinct PATH_NAME from VIP_APPLICATION_PATH)
;

[mcl]

tried it and got error

ORA-00904: invalid column name

[ales]

In order to get a better advice please send more about your tables and goals.
The query below is just a guess.

update ITX_APPLICATION_PATH
set
APPLICATION_ID =
(select A.APPLICATION_ID from ITX_APPLICATION A, VIP_APPLICATION_PATH B
where A.APPLICATION_NAME = B.APPLICATION_NAME
and ITX_APPLICATION_PATH.PATH_NAME = B.PATH_NAME
),
ITX_VERSION = ITX_VERSION+1,
(PATH_TYPE, PHYSICAL_PATH, DESCRIPTION, ITX_MODULE) =
(select B.PATH_TYPE, B.PHYSICAL_PATH, B.DESCRIPTION, B.ITX_MODULE
from VIP_APPLICATION_PATH B
where ITX_APPLICATION_PATH.PATH_NAME = B.PATH_NAME
)
where ITX_APPLICATION_PATH.PATH_NAME in (select distinct PATH_NAME from VIP_APPLICATION_PATH)