Updating a row based on a value from another table?

Question

So I have two tables "books" and "bookOrder" and they look something like this:

bookOrder

| orderID | book name   | required | availability
| --------|-------------| ---------| ------------|
| 1       |Harry Potter | 9        | yes         |
| 2       |Twilight     | 8        | yes         |
| 3       |Bible        | 8        | yes         |

books

|book name   |quantity|
|------------|--------|
|Harry Potter|10      |
|Twilight    |5       |
|Bible       |8       |

I want to be able to update the the "bookOrder" availability column based on the "books" table.

So far my SQL query is as follows:

UPDATE bookOrder
SET avalability = case when (
SELECT quantity
FROM books
WHERE books.bookName = bookOrder.bookName
) < (SELECT required From bookOrder WHERE bookOrder.bookName = books.bookName) THEN 'NO' END;

But I am getting an error:

missing FROM-clause entry for table "books"

I might just have the query completely wrong.

(P.S. I know it's completely ridiculous that anyone would want to buy 8 Twilight books.)

Answer 1

I would use an update join here:

UPDATE bookOrder bo
SET availability = CASE WHEN b.quantity < bo.required THEN 'NO' ELSE 'YES' END
FROM books b
WHERE b.bookName = bo.bookName

Answer 2

Use the UPDATE syntax as advised by Tim Biegeleisen, but add one more WHERE condition:

UPDATE bookOrder bo
SET    availability = CASE WHEN b.quantity < bo.required THEN 'NO' ELSE 'YES' END
FROM   books b
WHERE  b.bookName = bo.bookName
AND    bo.availability IS DISTINCT FROM CASE WHEN b.quantity < bo.required THEN 'NO' ELSE 'YES' END;

This way, Postgres only writes a new row version where the value actually changes. This can have a major impact on write performance - and general database performance. See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

availability should really be data type boolean, in which case you can simplify:

UPDATE bookOrder bo
SET    availability = (b.quantity >= bo.required)
FROM   books b
WHERE  b.bookName = bo.bookName
AND    bo.availability IS DISTINCT FROM (b.quantity >= bo.required);

If referential integrity is enforced with a FK constraint and all involved columns are defined NOT NULL, simplify some more:

...
AND    bo.availability <> (b.quantity >= bo.required);

Or drop the column completely and look up availability dynamically. Such an attribute can be outdated on arrival. You could use a VIEW for this:

CREATE VIEW book_order_plus AS
SELECT bo.*, (b.quantity >= bo.required) AS availability
FROM   bookOrder bo
LEFT   JOIN books b USING (bookName);

Oh, and use legal, lower-case, unquoted identifiers to keep your life with Postgres simple. See:

• Are PostgreSQL column names case-sensitive?