Update a column based on a field from another table

Question

Hi:

I'd like to update values in one table based on corresponding values from other tables. Say you want to update prices of pieces provided by one specific manufacturer whose name is in the table Manufacturers with the Pieces table containing only the id of the manufacturer.

I've seen several solutions for MySQL here and for MS SQL Server here but none of them seems to work in SQLite.

Any suggestion?

Answer 1

Have you tried something like this?

UPDATE Pieces
SET price = 42
WHERE manufacturer_id = (
    SELECT id
    FROM Manufacturers
    WHERE Name = 'FooBar Inc.'
)

Answer 2

For SQLite there is no JOIN functionality in UPDATE statements. The only option you have is to make corellated subqueries:

UPDATE pieces 
    SET price = (SELECT SUM(price)
                 FROM manufacturers
                 WHERE pieces.manufacture_id = manufacturers.id)
WHERE manufacture_id in (SELECT id
                         FROM manufacturers
                         WHERE name IN ('boo', 'foo'));

That is not very efficient, but you can adjust it to your needs.