[Solved] SQL: UPDATE number of rows using another table

EverSQL Database Performance Knowledge Base

SQL: UPDATE number of rows using another table

Can somebody please help me with this 'simple' query.

I have a two tables: Tunes and SavedRatings

Tunes contains a load of tune info including a Rating (INTEGER) field and an MD5 field used to uniquely identify a tune.

SavedRatings is a table consisting of a Rating and an MD5 field so that when I delete the contents of Tunes and add tunes back at a later date, I can identify the rating given to that tune.

So.. what I'm trying to do is update the Rating field in my Tunes table, by matching the Tunes MD5 field in the SavedRatings table.

I came up with the below command which is completely wrong. Can you please suggest an alternative? I'm using SQLite.

UPDATE Tunes
SET Tunes.Rating=SavedRatings.Rating
WHERE Tunes.MD5 IN (SELECT MD5 FROM SavedRatings);

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `Tunes` ADD INDEX `tunes_idx_md5` (`MD5`);
The optimized query:
SELECT
        Tunes.Rating 
    FROM
        Tunes 
    WHERE
        Tunes.MD5 IN (
            SELECT
                SavedRatings.MD5 
            FROM
                SavedRatings
        )

Related Articles



* original question posted on StackOverflow here.