I think this what you want


update plmqa.ecs_activity b
set (ECSACTIVITIES_UPDATED_DATE,
ECSACTIVITIES_UPDATED_BY,
ECSACTIVITIES_STATUS) =
(select sysdate, 0, 0
from plmqa.ecs_task a
WHERE a.ecsactivities_uid = b.ecsactivities_uid)
where UPPER (b.ecsactivities_subject) LIKE '%DELL%'
and b.ECSACTIVITIES_STATUS = '1'
and b.ECS_CREATED_BY = '0';

or

update plmqa.ecs_activity b
set (ECSACTIVITIES_UPDATED_DATE = sysdate,
ECSACTIVITIES_UPDATED_BY = '0',
ECSACTIVITIES_STATUS) = '0'
WHERE b.ecsactivities_uid in
(select a.ecsactivities_uid from plmqa.ecs_task a)
-- not certain what table this resides in --
and UPPER (b.ecsactivities_subject) LIKE '%DELL%'
--
and b.ECSACTIVITIES_STATUS = '1'
and b.ECS_CREATED_BY = '0';

or use a exists statment

Not sure of syntax for exists don't use often.

update plmqa.ecs_activity b
set (ECSACTIVITIES_UPDATED_DATE = sysdate,
ECSACTIVITIES_UPDATED_BY = '0',
ECSACTIVITIES_STATUS) = '0'
WHERE
-- not certain what table this resides in --
UPPER (b.ecsactivities_subject) LIKE '%DELL%'
--
and b.ECSACTIVITIES_STATUS = '1'
and b.ECS_CREATED_BY = '0'
AND EXISTS (SELECT 'X'
FROM plmqa.ecs_task a
WHERE a.ecsactivities_uid = b.ecsactivities_uid)

I think this is that syntax


Ron Backmann
Consulting Technical Analyst




"Derrick Pitts " <oracledba-ezmlmshield-x37445532.[Email address protected]

11/30/2005 11:20 AM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
Update statement






Gurus,

I need a little help with an update statement. I'm trying to create an
update statement that joins a table. Will something like this work?

update plmqa.ecs_activity
(SELECT a.ecsactivities_uid
FROM plmqa.ecs_task a, plmqa.ecs_activity b
WHERE a.ecsactivities_uid = b.ecsactivities_uid
AND UPPER (ecsactivities_subject) LIKE '%DELL%')
SET ECSACTIVITIES_UPDATED_DATE = sysdate,
ECSACTIVITIES_UPDATED_BY = '0',
ECSACTIVITIES_STATUS = '0'
where ECSACTIVITIES_STATUS = '1' and ECS_CREATED_BY = '0';

Derrick Pitts, OCP
Database Administrator
Palm Harbor Homes, Inc.
972-764-9403


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html




The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.

Oracle LazyDBA home page