CODEWITHSUNDEEP
oracle-database
MatteoS
MatteoS

Reputation: 95

Oracle - Deleting or inserting rows via cursor

Apologies in advance, I am occasional Oracle user. I have put together a lookup table used by various functions/procedures and need to keep refresh this once a day with rows that either need removing or inserting. I have put together the following simply queries that return the columns against which I can determine the required action. Once I have returned my deletion data, I then need to delete from table A all records where the site_id and zone_ids match. I cant figure out the best way to achieve this, I have thought about running the select statements as cursors, but am not sure how I then delete the rows from table A using the site_id and zone_id from each record returned.

Query That returns records to be deleted from Table_A

SELECT site_id,zone_id,upper(ebts_switch_name)
FROM Table_A
minus
    (SELECT site_id,zone_id, upper(ebts_switch_name)
    FROM Table_B@remote_db
    UNION
    SELECT site_id,zone_id,upper(ebts_switch_name)
    FROM Table_C);

Query That returns records to be Inserted into Table_A

SELECT cluster_id, site_id,zone_id, upper(trigram),upper(ebts_switch_name)
FROM Table_B@remote_db
WHERE site_id is NOT NULL
minus
SELECT cluster_name,site_id,zone_id,upper(trigram),upper(ebts_switch_name)
FROM Table_A

Upvotes: 0

Views: 190

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

You can use your statements directly in the manner shown below:

DELETE FROM TABLE_A
  WHERE (SITE_ID, ZONE_ID, UPPER(EBTS_SWITCH_NAME)) IN
    (SELECT site_id, zone_id, upper(ebts_switch_name)
       FROM Table_A
       minus
         (SELECT site_id, zone_id, upper(ebts_switch_name)
            FROM Table_B@remote_db
         UNION
         SELECT site_id, zone_id, upper(ebts_switch_name)
           FROM Table_C));

INSERT INTO TABLE_A (CLUSTER_NAME, SITE_ID, ZONE_ID, TRIGRAM, EBTS_SWITCH_NAME)
  SELECT cluster_id, site_id, zone_id, upper(trigram), upper(ebts_switch_name)
    FROM Table_B@remote_db
    WHERE site_id is NOT NULL
    minus
    SELECT cluster_name, site_id, zone_id, upper(trigram), upper(ebts_switch_name)
    FROM Table_A;

Best of luck.

Upvotes: 2

hinotf
hinotf

Reputation: 1138

I can't understand what do you mean by first query, cause it's almost same as

SELECT *
  FROM table_a
MINUS
SELECT *
  FROM table_a

means empty record set.

But generally, use DELETE syntax

DELETE
  FROM table_a
 WHERE (col1, col2) IN (SELECT col1, col2
                          FROM table_b);

And INSERT syntax

INSERT INTO table_a (col1, col2)
     SELECT col1, col2
       FROM table_b;

Upvotes: 1

Related Questions