How to use a CSV file to update all column values in a table?
<p>I wish to replace column values in a sqlite3 (Clementine) db table named <code>songs</code> using a CSV file I imported and named as a table <code>songsnew</code>. </p>

<p>The CSV file has three columns with headers that have fieldnames matching those in the target database.  The common column I want it to "index" to is <code>filename</code>.</p>

<p>Using SQLiteBrowser, I tried this SQL taken from another answer:</p>

<pre><code>update songs
set lastplayed = (select songsnew.lastplayed
where songsnew.filename = songs.filename)
where
exists (
select *
from songsnew
where songsnew.filename = songs.filename
)
</code></pre>

<p>It did not work. The error message says:</p>

<pre><code>no such column: songsnew.lastplayed: update songs
</code></pre>

<p>...which is odd because as I am typing that SQL into the SQLite database browser, it correctly identifies 'pops up' the column <code>filename</code> for the <code>songsnew</code> table.</p>

<p>Using SQLite3 and/or bash, can this be done only by importing the CSV as a table?  Which way is simplest and how is it done? I do not wish to manually update thousands of records one at a time.</p>

<p>As requested in the comments, here is first the schema <code>songs</code>:</p>

<pre><code>CREATE TABLE `songs` (
    `title` TEXT,
    `album` TEXT,
    `artist`    TEXT,
    `albumartist`   TEXT,
    `composer`  TEXT,
    `track` INTEGER,
    `disc`  INTEGER,
    `bpm`   REAL,
    `year`  INTEGER,
    `genre` TEXT,
    `comment`   TEXT,
    `compilation`   INTEGER,
    `length`    INTEGER,
    `bitrate`   INTEGER,
    `samplerate`    INTEGER,
    `directory` INTEGER NOT NULL,
    `filename`  TEXT NOT NULL,
    `mtime` INTEGER NOT NULL,
    `ctime` INTEGER NOT NULL,
    `filesize`  INTEGER NOT NULL,
    `sampler`   INTEGER NOT NULL DEFAULT '0',
    `art_automatic` TEXT,
    `art_manual`    TEXT,
    `filetype`  INTEGER NOT NULL DEFAULT '0',
    `playcount` INTEGER NOT NULL DEFAULT '0',
    `lastplayed`    INTEGER,
    `rating`    INTEGER,
    `forced_compilation_on` INTEGER NOT NULL DEFAULT '0',
    `forced_compilation_off`    INTEGER NOT NULL DEFAULT '0',
    `effective_compilation` TEXT NOT NULL DEFAULT '0',
    `skipcount` INTEGER NOT NULL DEFAULT '0',
    `score` INTEGER NOT NULL DEFAULT '0',
    `beginning` INTEGER NOT NULL DEFAULT '0',
    `cue_path`  TEXT,
    `unavailable`   INTEGER DEFAULT '0',
    `effective_albumartist` TEXT,
    `etag`  TEXT,
    `performer` TEXT,
    `grouping`  TEXT
);
</code></pre>

<p>...and here is the schema for the CSV file songsnew after importing:</p>

<pre><code>CREATE TABLE `songsnew` (
    `filename`  TEXT,
    `playcount` TEXT,
    `lastplayed`    TEXT
);
</code></pre>