• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3741
  • Last Modified:

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;
0
bbekele
Asked:
bbekele
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now