Oracle delete query

I am trying to do a delete query based on several table joins from oracle.. the access equivalent of this is DELETE FIELD_LOG.*
FROM (((computers INNER JOIN FIELD_LOG ON (computers.computer_id = FIELD_LOG.COMPUTER_ID) AND (computers.broker_id = FIELD_LOG.BROKER_ID)) INNER JOIN device_brokers ON computers.broker_id = device_brokers.broker_id) INNER JOIN property_value_log ON (FIELD_LOG.COMPUTER_ID = property_value_log.computer_id) AND (FIELD_LOG.DEVICE_ID = property_value_log.device_id) AND (device_brokers.broker_id = property_value_log.broker_id)) INNER JOIN (device_types INNER JOIN faults ON (device_types.device_type = faults.device_type) AND (device_types.device_version = faults.device_version)) ON FIELD_LOG.FIELD_ID = faults.fault_id;
bbekeleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ron WarshawskyCommented:
So?
0
bbekeleAuthor Commented:
How can I do the same thing in oracle
0
Gustavo Perez BuenrostroCommented:
bbekele,
Try next statement and let me know if it works as you need:

delete field_log
  from field_log
       join computers
            join device_brokers
              on computers.broker_id=device_brokers.broker_id
         on field_log.computer_id=computers.computer_id and
            field_log.broker_id=computers.broker_id
       join property_value_log
         on field_log.computer_id=property_value_log.computer_id and
            field_log.device_id=property_value_log.device_id
       join faults
            join device_types
              on faults.device_type=device_types.device_type and
                 faults.device_version=device_types.device_version
         on field_log.field_id=faults.fault_id
 where device_brokers.broker_id=property_value_log.broker_id
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sudhi022299Commented:
try it out:

delete from field_log where field_id in (
  select field_id from field_log, computers, device_brokers, property_value_log, device_types, faults
  where
((field_log.broker_id = computers.broker_id) and (field_log.computer_id = computers.computer_id)) and
 (computers.broker_id = device_brokers.broker_id) and
  ( (field_log.computer_id = property_value_log.computer_id) and (field_log.device_id = property_value_log.device_id) and (property_value_log.broker_id = device_brokers.broker_id)) and
( ( (device_types.device_type = faults.device_type) and (device_types.device_version = faults.device_version)) and (field_log.field_id = faults.fault_id) )
);

hope this helps you.

Does oracle support "join" clause ?

Regards,
Sudhi.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bbekeleAuthor Commented:
I tried a similar version of Sudhis query but it deleted the entire table. I am trying to populate te table again. The delete query should delete records returned by this select query.


sql1 = " SELECT Distinct SYSTEM_STRUCTURE.SYSTEM AS SYSTEM, " _
   & " SYSTEM_STRUCTURE.SUBSYSTEM AS SUBSYSTEM,SYSTEM_STRUCTURE.SUBSUBSYSTEM AS SUBSUBSYSTEM," _
   & " format(FIELD_LOG.LOG) AS LOG_DATE,DEVICE_BROKERS.HOSTNAME AS DEVICE_BROKER," _
   & " COMPUTERS.HOSTNAME AS COMPUTER, DEVICES.DEVICE_TYPE AS DEVICE_TYPE," _
   & " PROPERTY_VALUE_LOG.VAL AS DEVICE_NAME ,ANALOGS.NAM AS EVENT_NAME," _
   & " FIELD_LOG.VAL AS Event_Value, FIELD_LOG.LEV AS Event_Level" _
   & " FROM DEVICES, FIELD_LOG, ANALOGS, DEVICE_BROKERS, COMPUTERS," _
   & " PROPERTY_VALUE_LOG PROPERTY_VALUE_LOG, SYSTEM_STRUCTURE" _
   & " WHERE DEVICES.DEVICE_ID = FIELD_LOG.DEVICE_ID AND FIELD_LOG.FIELD_ID = ANALOGS.ANALOG_ID" _
   & " AND DEVICES.DEVICE_VERSION = ANALOGS.DEVICE_VERSION AND" _
   & " DEVICES.DEVICE_TYPE = ANALOGS.DEVICE_TYPE AND FIELD_LOG.BROKER_ID = DEVICE_BROKERS.BROKER_ID" _
   & " AND FIELD_LOG.COMPUTER_ID = COMPUTERS.COMPUTER_ID AND" _
   & " FIELD_LOG.BROKER_ID = PROPERTY_VALUE_LOG.BROKER_ID AND" _
   & " FIELD_LOG.COMPUTER_ID = PROPERTY_VALUE_LOG.COMPUTER_ID AND " _
   & " FIELD_LOG.DEVICE_ID = PROPERTY_VALUE_LOG.DEVICE_ID AND property_value_log.property_id = 1 and " _
   & " DEVICES.BROKER_ID = SYSTEM_STRUCTURE.BROKER_ID AND " _
   & " DEVICES.COMPUTER_ID = SYSTEM_STRUCTURE.COMPUTER_ID AND " _
   & " DEVICES.DEVICE_ID = SYSTEM_STRUCTURE.DEVICE_ID AND " _
   & " (FIELD_LOG.TYPE = 1) and " _
   & " analogs.DEVICE_TYPE = DEVICES.DEVICE_TYPE AND " _
   & " analogs.DEVICE_VERSION = DEVICES.DEVICE_VERSION "


Ben.
0
bbekeleAuthor Commented:
actually that was the access version the oracle version is

sql1 = " SELECT Distinct SYSTEM_STRUCTURE.SYSTEM AS SYSTEM, " _
   & " SYSTEM_STRUCTURE.SUBSYSTEM AS SUBSYSTEM,SYSTEM_STRUCTURE.SUBSUBSYSTEM AS SUBSUBSYSTEM," _
   & " to_char(Field_Log.log,'mm/dd/yyyy hh24:mm:ss') AS LOG_DATE,DEVICE_BROKERS.HOSTNAME AS DEVICE_BROKER," _
   & " COMPUTERS.HOSTNAME AS COMPUTER, DEVICES.DEVICE_TYPE AS DEVICE_TYPE," _
   & " PROPERTY_VALUE_LOG.VAL AS DEVICE_NAME ,ANALOGS.NAM AS EVENT_NAME," _
   & " FIELD_LOG.VAL AS Event_Value, FIELD_LOG.LEV AS Event_Level" _
   & " FROM DEVICES, FIELD_LOG, ANALOGS, DEVICE_BROKERS, COMPUTERS," _
   & " PROPERTY_VALUE_LOG PROPERTY_VALUE_LOG, SYSTEM_STRUCTURE" _
   & " WHERE DEVICES.DEVICE_ID = FIELD_LOG.DEVICE_ID AND FIELD_LOG.FIELD_ID = ANALOGS.ANALOG_ID" _
   & " AND DEVICES.DEVICE_VERSION = ANALOGS.DEVICE_VERSION AND" _
   & " DEVICES.DEVICE_TYPE = ANALOGS.DEVICE_TYPE AND FIELD_LOG.BROKER_ID = DEVICE_BROKERS.BROKER_ID" _
   & " AND FIELD_LOG.COMPUTER_ID = COMPUTERS.COMPUTER_ID AND" _
   & " FIELD_LOG.BROKER_ID = PROPERTY_VALUE_LOG.BROKER_ID AND" _
   & " FIELD_LOG.COMPUTER_ID = PROPERTY_VALUE_LOG.COMPUTER_ID AND property_value_log.property_id = 1 and " _
   & " FIELD_LOG.DEVICE_ID = PROPERTY_VALUE_LOG.DEVICE_ID AND" _
   & " DEVICES.BROKER_ID = SYSTEM_STRUCTURE.BROKER_ID AND " _
   & " DEVICES.COMPUTER_ID = SYSTEM_STRUCTURE.COMPUTER_ID AND " _
   & " DEVICES.DEVICE_ID = SYSTEM_STRUCTURE.DEVICE_ID AND " _
   & " (FIELD_LOG.TYPE = 1) and " _
   & " analogs.DEVICE_TYPE = DEVICES.DEVICE_TYPE AND " _
   & " analogs.DEVICE_VERSION = DEVICES.DEVICE_VERSION "
0
mshaikhCommented:
Try the following:

DELETE FROM FIELD_LOG  a
WHERE  a.ROWID IN (
     SELECT b.ROWID
     FROM computer, device_brokers, property_value_log,
              device_type, faults, FIELD_LOG b
     WHERE computers.computer_id = b.COMPUTER_ID
     AND computers.broker_id = b.BROKER_ID
     AND computers.broker_id = device_brokers.broker_id
     AND  b.COMPUTER_ID = property_value_log.computer_id
     AND  b.DEVICE_ID = property_value_log.device_id
     AND device_brokers.broker_id = property_value_log.broker_id
     AND device_types.device_type = faults.device_type
     AND device_types.device_version = faults.device_version
     AND  b.FIELD_ID = faults.fault_id);


If this does not do what you expect then ROLLBACK.
0
bbekeleAuthor Commented:
With a little tweaking sudhi's suggestion has worked. Thanks.

Ben
0
bkowalskiCommented:
Ben, could you post what worked so we can all learn from this.  Thanks.
-Brad
0
bbekeleAuthor Commented:
Here it is...
sql = "delete from field_log where field_id in " _
& " (select field_id from field_log, computers, device_brokers, property_value_log, device_types, faults" _
& " Where ((field_log.broker_id = computers.broker_id) and (field_log.computer_id = computers.computer_id)) and " _
& " (computers.broker_id = device_brokers.broker_id) and " _
& " ( (field_log.computer_id = property_value_log.computer_id) and (field_log.device_id = property_value_log.device_id) and (property_value_log.broker_id = device_brokers.broker_id)) and " _
& "( ( (device_types.device_type = faults.device_type) and (device_types.device_version = faults.device_version)) and (field_log.field_id = faults.fault_id) ))and field_log.type = 2"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.