Updating a row based on a value from another table?-CodePudding

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.

Thanks in advance.

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

(P.S again - I am using Postgres)

CodePudding user response：

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