MySQL - Many UPDATEs to run after INSERT

Question

I have a basic CRUD application where people are adding records. When they INSERT someone new, there are many updates (about 100) that need to be done to the record after it's been added.

I have tried to put the UPDATE statements in a TRIGGER but it would either lock the table --OR-- give me the "can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger" error.

Then I tried to put them all in a PROCEDURE -- which took about 30 seconds to run. Unacceptable.

I don't think I'm doing anything difficult here. Mostly, the updates are updating one field based on another.

Example

INSERT INTO data_table (field1, field2, ...) VALUES ('$field1', '$field2', ...);

Then the updates:

UPDATE data_table SET field2 = 'New York City' WHERE field1 = '212';
UPDATE data_table SET field2 = 'Los Angeles' WHERE field1 = '213';

etc.

Would it be faster/more efficient to do a bunch of IF...THEN statements in PHP to change the variable before inserting?

I'm just trying to speed up the process of inserting the record and processing the updates. Any help?

Answer 1

Let us suppose that you have built a reference data set that has two (or more) fields. For the sake of argument lets call the table geography

• Area_Code
• City

The MySQL update syntax would be

UPDATE data_table, geography
SET data_table.field2 = geography.City
WHERE data_table.field1 = geography.Area_Code

Potential pitfalls to watch out for are the data types, size and collation discrepancies between data_table.field1 and geography.Area_Code.

If geography.Area_Code is unique and must always be present make sure you stick a primary key on it.

It is probably worth adding an index to data_table.field1. Again, if it is mandatory and unique make it a primary key.