asked on

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;

Ron Warshawsky

So?

bbekele

ASKER

How can I do the same thing in oracle

Gustavo Perez Buenrostro

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

ASKER CERTIFIED SOLUTION

sudhi022299

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bbekele

ASKER

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.

bbekele

ASKER

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 "

mshaikh

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.

bbekele

ASKER

With a little tweaking sudhi's suggestion has worked. Thanks.

Ben

bkowalski

Ben, could you post what worked so we can all learn from this. Thanks.
-Brad

bbekele

ASKER

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"