Cross-table UPDATE in SQLITE3

Question

In SQL Server, I can do something like this:

UPDATE tbl1 SET col2 = tbl2.col2 
FROM table1 tbl1 INNER JOIN table2 tbl2 ON tbl1.col1 = tbl2.col1

I haven't bothered to look whether this is part of any SQL standard or not, and I'm sure there are other ways to do it, but it is astoundingly useful.

Here's my problem. I need to do something similar in SQL (i.e, not a host language) with SQLITE3. Can it be done?

Answer 1

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

Answer 2

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

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

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

Answer 3

This works for sqlite:

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