vendredi 6 novembre 2015

SQLite update query with two tables with foreign key

In an sqlite database I have two tables: "substances" and "densities" with columns "id, name" (for substances table) and "id, substanceid, density" (for densities table). substanceid has a foreign key (references id of the substances table). substanceid is UNIQUE, so there are the same amount of rows in both tables (each row corresponding to one substanceid)

I want to update density and name for a certain substanceid, i.e. one row in each table, but struggle a bit with the syntax for updating the substances table.

The densities table I can update with:

UPDATE densities
SET density = obj.getDensity()
WHERE id = obj.getId();

The obj contains (id, density, name), but not substanceId.

For the substances table I would like to write something like:

UPDATE substances
SET name = obj.getName()
WHERE id = ???

And the "???" in prose would be:

densities.substanceid for which densities.i = obj.getId

How do I write this in SQLite?

Aucun commentaire:

Enregistrer un commentaire