CODEWITHSUNDEEP
sqlsqlite
Sergio
Sergio

Reputation: 4567

Update a column based on a field from another table

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?

Upvotes: 6

Views: 2452

Answers (2)

newtover
newtover

Reputation: 32094

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.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838186

Have you tried something like this?

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

Upvotes: 2

Related Questions