How to Efficiently Update Multiple Tables with Similar Values in MySQL?

Updating Multiple Tables with Similar Values in MySQL

In the scenario where you have two tables requiring the same updates for denormalization, you can utilize multi-table updates offered by MySQL. This approach allows for simultaneous updates across multiple tables with a single statement.

To achieve this:

1. Identify the Common Columns: Determine which columns in both tables need to be updated with the same values.
2. Join the Tables: Use an INNER JOIN to connect the tables based on the common columns (e.g., userid).
3. Specify the Updates: In the SET clause, list the columns to be updated and their corresponding values. Ensure that the values are consistent across both tables.
4. Filter the Results: Add any necessary conditions (e.g., WHERE clauses) to filter the specific rows to be updated.

For instance, consider the following query:

UPDATE Table_One a INNER JOIN Table_Two b ON (a.userid = b.userid)
SET
  a.win = a.win+1, a.streak = a.streak+1, a.score = a.score+200,
  b.win = b.win+1, b.streak = b.streak+1, b.score = b.score+200 
WHERE a.userid = 1 AND a.lid = 1 AND b.userid = 1

Here, both tables' win, streak, and score columns are updated incrementally for a specific userid and lid value. Note that the lid column is not present in Table_Two, so its value is not updated.

Additional Considerations:

• Multi-table updates do not support LIMIT, so updates may affect more rows than intended.
• Stored procedures or transactions might provide better control in certain situations.

The above is the detailed content of How to Efficiently Update Multiple Tables with Similar Values in MySQL?. For more information, please follow other related articles on the PHP Chinese website!