Correlated Update Statement
My students wanted some additional examples on correlated update statements. I’ve been struggling with what the right way may be to illustrate them.
Correlated subqueries are a hard concept to explain to those new to SQL. While correlated update statements seem impossibly obscure to many or inordinately complex. New SQL developers often flee to the comfort of procedural programs when it comes to update statements.
This uses my video store data model. It’s a correlated update statement to clean up potential corrupt data. More or less something a DBA might run to ensure a business rule hasn’t been violated over time. It checks for the correct foreign key value in a table when a dependent table contains one or more than one row of data.
The aqua-green box highlights a subquery that aggregates foreign key columns and groups the result with the foreign key value. The results from this subquery become a run-time view or derived table. The result set is a foreign key value and a substitute string literal value for each row in the contact table. These results correlate to the update statement’s rows based on the input parameter. The input parameter is a column from each updated row.
A unique key (or check constraint) exists on the combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns. This ensures that only one row is returned and assigned to the member_type column in the member table. The update statement naturally works in either Oracle or MySQL without any porting changes.
Correlated Update Statement ↓
Expand this section to see the clear text for the foregoing image.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | UPDATE member m SET member_type = (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'MEMBER' AND common_lookup_column = 'MEMBER_TYPE' AND common_lookup_type = (SELECT dt.member_type FROM (SELECT c.member_id , CASE WHEN COUNT(c.member_id) > 1 THEN 'GROUP' ELSE 'INDIVIDUAL' END AS member_type FROM contact c GROUP BY c.member_id) dt WHERE dt.member_id = m.member_id)); |
While this type of solution is powerful in its own right, I thought it might be interesting to see their procedural equivalents. These correlated subqueries run for each row returned by the master query (or outermost statement). Therefore, they act like functions.
Procedural equivalents (or user-defined functions) simplify the update statement like so:
UPDATE member m SET member_type = get_member_type(m.member_id); |
If you’re interested in seeing how you would implement this solution in a user-defined function, just expand the dropdown that interest you.
Oracle User-Defined Function (UDF) ↓
Expand this section to see how to map this logic to a PL/SQL schema-level function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | CREATE OR REPLACE FUNCTION get_member_type (pv_member_id NUMBER) RETURN NUMBER IS -- Define a collection of strings. TYPE type_options IS TABLE OF VARCHAR2(10); -- Declare local variables. lv_dependent NUMBER := 1; lv_return_id NUMBER; -- Declare a local variable of the collection. lv_member_type TYPE_OPTIONS := type_options('INDIVIDUAL','GROUP'); -- Define a dynamic cursor to count the number of foreign key values. CURSOR count_contact (cv_member_id NUMBER) IS SELECT COUNT(c.member_id) FROM contact c WHERE c.member_id = cv_member_id; -- Define a dynamic cursor to find a key for an individual or group member type. CURSOR get_lookup_id (cv_type VARCHAR2) IS SELECT common_lookup_id FROM common_lookup WHERE common_lookup_context = 'MEMBER' AND common_lookup_type = cv_type; BEGIN -- Get the number of foreign key values for a contact. OPEN count_contact(pv_member_id); FETCH count_contact INTO lv_dependent; CLOSE count_contact; -- Open the dynamic cursor with the required value. IF lv_dependent = 1 THEN OPEN get_lookup_id(lv_member_type(1)); ELSE OPEN get_lookup_id(lv_member_type(2)); END IF; -- Get the correct surrogate primary key value. FETCH get_lookup_id INTO lv_return_id; CLOSE get_lookup_id; -- Return the correct primary key for use as a foreign key. RETURN lv_return_id; END; / |
MySQL User-Defined Function (UDF) ↓
Expand this section to see how to map this logic to a Persistent Stored Module (PSM) function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | SELECT 'DROP FUNCTION IF EXISTS get_member_type' AS "Statement"; DROP FUNCTION IF EXISTS get_member_type; SELECT 'DELIMITER $$' AS "Statement"; DELIMITER $$ SELECT 'CREATE FUNCTION get_member_type' AS "Statement"$$ CREATE FUNCTION get_member_type(pv_member_id INT) RETURNS INT BEGIN /* Define three local variables. */ DECLARE lv_contact_number INT; DECLARE lv_member_type CHAR(30); DECLARE lv_return_value INT; /* Define a dynamic cursor to count the number of foreign key values. */ DECLARE contact_cursor CURSOR FOR SELECT COUNT(c.member_id) FROM contact c WHERE c.member_id = pv_member_id; /* Define a dynamic cursor to find a key for an individual or group member type. */ DECLARE common_lookup_cursor CURSOR FOR SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'MEMBER' AND common_lookup_column = 'MEMBER_TYPE' AND common_lookup_type = lv_member_type; /* Get the number of foreign key values for a contact. */ OPEN contact_cursor; FETCH contact_cursor INTO lv_contact_number; CLOSE contact_cursor; /* Assign group membership when more than one foreign key value is found; and assign individual membership when only one foreign key value is found. */ IF lv_contact_number > 1 THEN SET lv_member_type = 'GROUP'; ELSE SET lv_member_type = 'INDIVIDUAL'; END IF; /* Get the correct surrogate primary key value. */ OPEN common_lookup_cursor; FETCH common_lookup_cursor INTO lv_return_value; CLOSE common_lookup_cursor; /* Return the correct primary key for use as a foreign key. */ RETURN lv_return_value; END; $$ SELECT 'DELIMITER $$' AS "Statement"$$ DELIMITER ; |
You can query the results of the update statement with the following.
Change Confirmation Query ↓
Expand this section to see the query that lets you examine the changes. It runs in either Oracle or MySQL without any changes.
1 2 3 4 5 6 7 8 9 10 11 | SELECT m.member_id , dt.quantity , m.member_type , cl.common_lookup_type FROM member m JOIN (SELECT member_id , COUNT(c.member_id) AS quantity FROM contact c GROUP BY c.member_id) dt ON m.member_id = dt.member_id JOIN common_lookup cl ON m.member_type = cl.common_lookup_id ORDER BY m.member_id; |
As always, I look forward to helping and gaining insight.