On 2016/01/24 10:15 PM, audio muze wrote:
> I have a large table with ~350k records for which I'm in the process of
> standardising data.
350k records is not exactly a "large" table, A table scan would complete
in a couple of seconds on a normal system. 350 million rows are more
substantial and would require more care.
The rest of your explanation is a bit hard to follow, but if I
understand correct, You have a field (albumartist) which you have
somehow corrected from the original by creating a new table
(albumartists) with the old and new fields.
Once the field has been corrected and saved (in newalbumartist) you
would like to update these new values to the original database but fear
messing things up (a good fear) and so would like to know whether it is
safe to run the query, which you've already made and which seemingly
works, on the original DB without making mistakes and you want us to
tell you whether we see any problems that might bite you looking at your
update query. Right?
I have some weird news for you - We don't really know, nobody here is
above making mistakes, so what we all do is make backups and then try
the big updates and restore where necessary, until it works.
There are however better ways to do that update more directly, but first
things first: You need to back-up the database. Luckily in SQLite you
can simply close all open connections to it and copy the files to
another folder. 350k records should be a small file. You may optionally
use a DB Manager to back it up - I am not familiar with SQLite Studio
but I'm sure they have it. If not, you could try SQLitespeed (which
definitely has it and you can back up multiple versions and test-run
scripts before committing).
Your update query seems fine - if I may add one comment, please qualify
all the field references so you can't refer a wrong field that might end
up in scope. I doubt your query will have that problem, but it is a
safer practice.
Something like this perhaps:
UPDATE audio SET audio.albumartist = (
SELECT A.newalbumartist
FROM albumartists AS A
WHERE A.albumartist = audio.albumartist
)
WHERE audio.albumartist IN (SELECT B.albumartist FROM albumartists AS B);
Should do the trick - but find a way to check after the run completes,
and restore a backup if needed and retry or ask again if things seem awry.
Cheers,
Ryan
