CODEWITHSUNDEEP
sqlsqlitesql-update
Gregory Higley
Gregory Higley

Reputation: 16558

Cross-table UPDATE in SQLITE3

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?

Upvotes: 11

Views: 19830

Answers (5)

P-Gn
P-Gn

Reputation: 24591

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

Your example becomes:

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

Upvotes: 1

apjs
apjs

Reputation: 51

Just to emphasize Geogory Higley's post:

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

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

http://www.mail-archive.com/[email protected]/msg27207.html

The code 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 seem to be many posts at different sites that recommend the first approach so it is a bit confusing. I would suggest you test your output very carefully if you use this method which does seem faster and may work with matched tables.

Upvotes: 5

Trey Jackson
Trey Jackson

Reputation: 74430

This works for sqlite:

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

Upvotes: 24

Bill Karwin
Bill Karwin

Reputation: 562368

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.

Upvotes: 1

Gregory Higley
Gregory Higley

Reputation: 16558

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.

Upvotes: 1

Related Questions