December 27, 2009

MySQL - Using SELECT to UPDATE records.

I've been experimenting with MySQL over the holidays, specifically evolution of a db schema and how to roll out changes effectively. I setup a scenario where I had a mapping table with two ids. One id from table 1 and one id from table 2. Now, let's assume table 1 and table 2 no longer need a many to many mapping relation, and we can get away with table 2 having a direct foreign key relation to table 1 on a per-record basis. (We also assume there is no many to many information in the mapping table, so, table 1 and table 2 only have on occurence)

So our scenario is, we're moving from a table structure like this:
Table 1: id, value 1, value 2
Mapping: table 1 id, table 2 id
Table 2: id, value 1, value 2

to this:

Table 1: id, value 1, value 2
Table 2: id, table 1 id (FK), value 1, value 2

So I went ahead and altered table 2, but kept the mapping table intact as it still had all the information in it. I thought at first, I could write a script that just took the values from the mapping and ran the updates on table 2 so that the table 1 id from the mapping table was inserted into table 2. What would be the fun in that, if I can't learn to flex my SQL muscles...

I know that MySQL supports nested SELECTs on INSERT statements, but found out that p to and including MySQL 5.5 you can not use a nested SELECT for use in an UPDATE. So, I figured this was a simple work around:

SELECT @TABLE1_ID:=table_1_id, @TABLE2_ID:=table_2_id FROM mapping_table FOR UPDATE;
UPDATE table_2 SET table_1_id = @TABLE1_ID WHERE id = @TABLE2_ID;

This didn't quite work as I expected it would... Result:

SELECT @TABLE1_ID:=table_1_id, @TABLE2_ID:=table_2_id FROM mapping_table FOR UPDATE;# Rows: 3
UPDATE table_2 SET table_1_id = @TABLE1_ID WHERE id = @TABLE2_ID;# 1 row(s) affected.

Looks like it used the highest @TABLE2_ID from the mapping table and ran the update on that id. Once.

What started as something "simple" had now turned into re-learning SQL procedures and cursors.

This cursor takes care of the limitation encountered above where only the first row in the result set was being updated. Please note this is meant for command line mysql as the creation of this procedure relies on the 'delimiter ' instruction.


DROP PROCEDURE IF EXISTS fix_mapping;
delimiter //
CREATE PROCEDURE fix_mapping()
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE table1_id, table2_id INT;
DECLARE select_cursor CURSOR FOR SELECT mapping_one, mapping_two FROM mapping_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN select_cursor;

FETCH select_cursor INTO table1_id, table2_id;
WHILE
 finished <> 1
DO
 UPDATE table2 SET value_from_mapping_two = table2_id WHERE id = table1_id;
 FETCH select_cursor INTO table1_id, table2_id;
END WHILE;

CLOSE select_cursor;
END//
delimiter ;
CALL fix_mapping;

Note: If you encounter 'ERROR 1436 (HY000): Thread stack overrun:' go increase the thread_stack value in your my.cnf file. Mine was set to 64K, the increase to 256K worked for me.

You can validate that your procedure has been created by running: SHOW PROCEDURE STATUS;

When the procedure ran, all the values in the table2.value_from_mapping_two were correct and I could remove the mapping table. Also, don't forget to remove your procedure, as it won't work with the now deleted mapping table, accomplish this by running DROP PROCEDURE `table`.`procedure_name`

No comments:

Post a Comment