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.


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';


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?

  • @Mr_Thomas , 100 update in same tables or different tables ?
    – parlad
    Commented Apr 17, 2018 at 15:44

1 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.

  • Yes (in this example) the Area_Code is unique. I made it a Primary Key and re-wrote the statements like you suggested. 30 seconds down to 3! More than acceptable.
    – Mr_Thomas
    Commented Apr 17, 2018 at 15:26
  • @Mr_Thomas - 3 is unacceptable for a trivial update. What indexes do you have on the tables? Are there 99 more updates?
    – Rick James
    Commented May 4, 2018 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.