CODEWITHSUNDEEP
sqloracle-database
Christian Wilkie
Christian Wilkie

Reputation: 3823

Delete matching primary key values between two tables

The Problem:

I'm trying to delete rows in a table if the primary key values in a row match a row in a different table. For example, say I have table TABLE1 and table TABLE2 with primary keys indicated by *:

TABLE1 before:
Col A*| Col B*| Col C| Col D
__________________________
1      a         x      x 
2      a         x      x 
3      a         x      x 
3      b         x      x 

TABLE2:
Col A*| Col B*| Col C| Col D
__________________________
1      a         y      y 
4      a         y      y 
5      a         x      x

I want to delete any rows from TABLE1 where the primary key values (Col A/Col B) match TABLE2, then copy over all data in TABLE2 to TABLE1. In this case, the first row of TABLE1 has matching primary key values with TABLE2, so only that row would be deleted:

TABLE1 after:
Col A*| Col B*| Col C| Col D
__________________________
2      a         x      x 
3      a         x      x 
3      b         x      x

Code so far:

The following query seems to work for selecting the values to be deleted:

SELECT * FROM TABLE1 WHERE EXISTS (
SELECT * FROM TABLE2
WHERE TABLE1.A = TABLE2.A
AND TABLE1.B = TABLE2.B)

And to delete the rows I believe I could change it to DELETE FROM instead of SELECT * FROM. However, I want to try and avoid hardcoding the primary key columns so that I could use this query on different tables and just change the table names.

I have the following query that will select the names of the primary key columns:

SELECT COLUMN_NAME FROM (
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'TABLE1'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position)

But I'm not sure how to combine this with my other statement (or if this is possible). I'm also open to other ideas on any other possible solutions. Thanks!

Upvotes: 3

Views: 864

Answers (1)

Codeman
Codeman

Reputation: 12375

It sounds like you want to use Dynamic SQL.

Dynamic SQL lets you select columns and other things on the fly by building the query, rather than having a stored procedure that is executed every time. There are some challenges, but it can be a useful tool where necessary. You do need to be somewhat more careful about SQL injection attacks with it.

Upvotes: 1

Related Questions