Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Thursday, November 10, 2011

Joined Criteria for an Oracle Delete

This post contains an example of deleting from an Oracle table restricted to records defined in a subquery

The FROM argument to an oracle DELETE statement can be a subquery as well as the more common table.

DELETE FROM (
  SELECT * FROM tbl_j j
  JOIN tbl_a a ON (a.a_id = j.a_id)
  JOIN tbl_b b ON (b.b_id = j.b_id)
  WHERE a.a_name = 'A RECORD'
  AND b.b_name = 'B_RECORD'
)

tbl_a and tbl_b are tables with a name column: a_name and b_name.  These tables are related by a join table, tbl_j bu their IDs: a_id and b_id.  The following are the schemas for the table.

tbl_a ( a_id, a_name, .... )
tbl_b (b_id, b_name, ... )
tbl_j (a_id, b_id )

If the logic surround the deletion is too complex, this can be moved to a PL/SQL block that iterates over the candidate records and issues a DELETE using only a WHERE clause

DELETE FROM tbl_j WHERE a_id = ? AND b_id = ?

No comments:

Post a Comment