DELETE statement usage

The DELETE statement is used to delete rows in a table. DELETE operations are cascaded to all child segments.

Examples of valid IMS Universal JDBC driver DELETE statements

Deleting an entire database

The following statement deletes the HOSPITAL database:

DELETE FROM pcb01.HOSPITAL

Deleting a root

The following statement deletes the root segment instance and all its children in the hierarchic path.

DELETE FROM pcb01.HOSPITAL
WHERE HOSPCODE = 'H5140070000H'

Deleting a single record

Foreign keys allow the IMS Universal JDBC driver to maintain referential integrity by identifying the exact record (or segment instance) to delete. The following statement deletes a single record from the WARD table. In this example, the WARD table has the foreign key HOSPITAL_HOSPCODE. The WARD record will be deleted if and only if there is a HOSPCODE in the HOSPITAL table with the value of 'H5140070000H' and a WARD table with a WARDNO value of '0001'.

DELETE FROM pcb01.WARD
WHERE HOSPITAL_HOSPCODE = 'H5140070000H'
   AND WARDNO = '0001'

Deleting multiple records

The following statement deletes multiple records from the PATIENT table. In this example, the PATIENT table has two foreign keys: HOSPITAL_HOSPCODE and WARD_WARDNO. The PATIENT record will be deleted if and only if there is a HOSPCODE in the HOSPITAL table with the value of 'H5140070000H', a WARD table with WARDNO value of '0001', and a PATIENT table with a PATNUM value greater than '0007'.

DELETE FROM pcb01.PATIENT
WHERE PATNUM > '0007'
   AND HOSPITAL_HOSPCODE = 'H5140070000H'
   AND WARD_WARDNO = '0001'

The following statement deletes all WARD segment instances in the entire database:

DELETE FROM pcb01.WARD