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);
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `Tunes` ADD INDEX `tunes_idx_md5` (`MD5`);
SELECT Tunes.Rating FROM Tunes WHERE Tunes.MD5 IN ( SELECT SavedRatings.MD5 FROM SavedRatings )