SQLite update table with entries from a subset of records in another table

Question

I have the following two tables. The 'tracks' table has no artist/track names (thery are all currently NULL). I want to update 'tracks' with the artist_name and track_name from the 'names' table. The 'names' table has 242,416,787 records and the tracks table has about 4 million. The arist/track name is associated by track_id, so I only want entries form 'names' that I have track_ids for.

CREATE TABLE tracks (
        s3_url VARCHAR(128),
        track_id INTEGER KEY,
        cluster_id INTEGER KEY,
        rank INTEGER KEY,
        group_id VARCHAR(128),
        artist_name VARCHAR(128),
        track_name VARCHAR(128),
        set_name VARCHAR(128), 
        file_size INTEGER KEY);

CREATE TABLE names (
        artist_name VARCHAR(128), 
        track_name VARCHAR(128), 
        track_id INTEGER KEY, 
        album_name VARCHAR(128));

Here's what I have so far, this gets me the records in 'names' that I have track_ids for:

SELECT names.artist_name, f.track_id FROM names INNER JOIN tracks AS f ON names.track_id=f.track_id

I can't get figure out how to then stuff those results back into the 'tracks' table. I was trying something to the effect of the following:

UPDATE x SET artist_name=SELECT names.artist_name, f.track_id FROM names INNER JOIN tracks AS f ON names.track_id=f.track_id) AS x;

These threads here, and here to accomplish similar things and show that JOIN/UPDATE is not supported in SQLite.

The desired end result is to populate all entries in 'tracks' with artist_name and track_name from 'names'.

Don't update. Join when you need to look up the values. – Gordon Linoff Jun 06 '17 at 01:38 — Gordon Linoff, Jun 06 '17 at 01:38

score 0 · Accepted Answer · answered Jun 06 '17 at 06:47

Try this:

replace into tracks
  (rowid, s3_url, track_id, cluster_id, rank, group_id, artist_name, track_name, set_name, file_size)
select 
  t.rowid, t.s3_url, t.track_id, t.cluster_id, t.rank, t.group_id, n.artist_name, n.track_name, t.set_name, t.file_size
from names n
inner join tracks t on n.track_id = t.track_id
;

Available as a working example here: http://sqlfiddle.com/#!5/573148/1

SQLite update table with entries from a subset of records in another table

1 Answers1