Of course, if there was a FROM clause for UPDATE it would be as simple as:
UPDATE target SET resource = source.resource FROM source WHERE source.id = target.id AND source.resource IS NOT NULL; This is semantically the same as: SELECT ... FROM target, source WHERE source.id = target.id AND source.resource IS NOT NULL; except that instead of returning rows, where a row would be returned the update is performed. Now if SQLite could do an UPDATE FROM that would be really nice. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf > Sent: Saturday, 16 January, 2016 00:31 > To: SQLite mailing list > Subject: Re: [sqlite] Updating the contents of a field in table A with the > value of the same field in table B > > > 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); > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
