[ACCEPTED]-Cross-table UPDATE in SQLITE3-sql-update

Accepted answer

Score: 24

This works for sqlite:

UPDATE tbl1 SET col2 = (SELECT col2 FROM tbl2 WHERE tbl2.col1 = tbl1.col1)

Score: 5

Just to emphasize Geogory Higley's post:

I 10 have had problems with UPDATE tbl1 SET col2 = (SELECT col2 FROM tbl2 WHERE tbl2.col1 = tbl1.col1) where it updates 9 columns in tbl1 that do not exist in tbl2.

see 8 cheetah post at http://sqlite.phxsoftware.com/forums/p/1708/7238.aspx which points to:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg27207.html

The code 7 is:

insert or replace into foo (id, name, extra)
select bar.id, bar.name, foo.extra
  from bar 
  left join foo 
    on bar.id = foo.id;

and this seems to work correctly. There 6 seem to be many posts at different sites 5 that recommend the first approach so it 4 is a bit confusing. I would suggest you 3 test your output very carefully if you use 2 this method which does seem faster and may 1 work with matched tables.

Score: 1

I've discovered this can be done with INSERT OR REPLACE INTO. A 2 little more verbose than T-SQL's equivalent, but 1 just as handy.

Score: 1

For what it's worth, Microsoft SQL Server 9 and MySQL are the only brands of database 8 that support multi-table updates, and the 7 syntax each uses is not similar.

This feature 6 is not part of standard SQL. So it's not 5 surprising that support for multi-table 4 update (and delete) is nonstandard and not 3 supported by many brands.

Anyway, I'm glad 2 you found a solution that works for your 1 task.

Score: 1

Since version 3.33, SQLite supports the 5 UPDATE FROM idiom, however in a slightly different 4 flavour than that of SQL Server: the target 3 table must not be listed in the FROM cause, meaning 2 that joins with it must be done in a WHERE clause.

Your 1 example becomes:

UPDATE tbl1 
   SET col2 = tbl2.col2 
  FROM tbl2
 WHERE tbl1.col1 = tbl2.col1

Source: stackoverflow.com

[ACCEPTED]-Cross-table UPDATE in SQLITE3-sql-update

SQLite inner join - update using values from another table

How to update an SQLite database with a search and replace query?

UPDATE syntax in SQLite

SQLite - Create Trigger for Insert or Update

CASE vs Multiple UPDATE queries for large data sets - Performance

How to update a part of a timestamp field in postgres?

Update Table with a "Select query" with a where clause

How to copy text column value from one row to another row in mySQL

MySQL: Update all rows in a table matching results of another query

EntityFramework, Insert if not exist, otherwise update