On Friday, 15 January, 2016 22:58, audio muze <audiomuze at gmail.com> said:
> Apologies if I'm asking an obvious question, but I've searched and
> tried various options and have not been able to arrive at an UPDATE
> statement in SQLite that does what I need.
> I've two tables with a unique identifier that enables me to link them.
> I'm wanting to update the values of a particular field in table
> "target" with the contents of a corresponding field in table "source"
> where the corresponding field in table "source" is not null.
> target [ID, resource]
> source [ID, resource]
> Both tables are indexed on ID, which is a unique identifier
> There may or may not be a record with a matching ID in table "source"
> Where the ID's match (and resource in "source" is not NULL) I want to
> overwrite the corresponding resource field in "target" with the
> content of the resource field of the matching record of "source"
> I've tried the following statement, but the end result is all records
> in target get the same resource value written:
> UPDATE target
> SET resource = (
> SELECT t.resource FROM target t
> WHERE ( t.ID = ID AND t.resource IS NOT NULL )
> );
Try to describe the problem using keywords from SQL.
Both tables are indexed on ID, which is a unique identifier for
matching records between target and source.
For each record in target where there exists a record in source
and the source resource value is not null, set the resource field
in target to the resource value from the corresponding source
resource value.
Then, it is just translating from English into another language,
in this case SQL. And the translation is almost direct with just
a bit of re-ordering to conform to the syntax requirements of SQL.
UPDATE target
SET resource = (SELECT resource
FROM source
WHERE id = target.id)
WHERE EXISTS (SELECT 1
FROM source
WHERE id = target.id
AND resource IS NOT NULL);
