Home > Tips & Tricks > Fast update loads of records in your database

Fast update loads of records in your database

October 28th, 2010 Leave a comment Go to comments

When you have a database and you want to update a record you simply use an update statement like:

UPDATE table_name
SET col1 = col1 + 1, col2 = col1
WHERE col3 = 'something'

But what about when you want to update thousands of data and the where clause in your statement change continuously?

Let’s assume you have a table called ‘user_activity’:

id user_id ranking
1 23 66237
2 24 452763
3 25 23546345
4 26 87645

The ranking for our users could be calculated programmatically and then in order to update the results we would use the following statements

UPDATE user_activity
SET ranking = 12354
WHERE user_id = 23;

UPDATE user_activity
SET ranking = 5874252
WHERE user_id = 24;

UPDATE user_activity
SET ranking = 47821
WHERE user_id = 25;

UPDATE user_activity
SET ranking = 58214775
WHERE user_id = 26;

Now imaging that the table_activity has more columns, more where clauses (like date for example) and more records.

Something different and faster could be the following approach:

  1. Create a temporary table
  2. BULK insert your calculated results and also any other necessary columns
  3. Update target table using the temporary one
  4. Delete temporary table

Here is the code for our approach:

-- Create temporary table
CREATE TEMPORARY table 'user_activity_temp' (
    user_id int NOT NULL,
    ranking int NOT NULL
);

-- BULK Insert values to the temporary table
INSERT INTO user_activity_temp VALUES
(23, 123454), (24, 5874252), (25, 47821), (26, 58214775);

-- Update target table with the temorary's table results
UPDATE user_activity ua, user_activity_temp uat
SET ua.ranking = uat.ranking
WHERE ua.user_id = uat.user_id;

-- Delete your temporary table
DROP user_activity_temp;
Categories: Tips & Tricks Tags: ,