SQL Error: ORA-00971: missing SET keyword or SQL Error: ORA-00933: SQL command not properly ended

Question

I try to modify the status of the rentals to 'RENTED' when it appears as available but the title_copy corresponding was not returned.

The first query I tried(SQL Error: ORA-00971: missing SET keyword):

UPDATE
    title_copy_2 tc2,
    rental r
SET
    tc2.status = 'RENTED'
WHERE
    tc2.copy_id = r.copy_id and tc2.title_id = r.title_id and
    r.act_ret_date is null and tc2.status = 'AVAILABLE';

The second one(SQL Error: ORA-00933: SQL command not properly ended)

UPDATE
    tc2
SET
    tc2.status = 'RENTED'
FROM
    title_copy_2 tc2
INNER JOIN
    rental r
ON 
    tc2.copy_id = r.copy_id and tc2.title_id = r.title_id and 
    r.act_ret_date is null and tc2.status = 'AVAILABLE';

Answer 1

That's wrong syntax for Oracle; perhaps you'd rather use merge, e.g.

MERGE INTO title_copy_2 tc2
     USING (SELECT r.copy_id, r.title_id
              FROM rental r
             WHERE r.acct_ret_date IS NULL) x
        ON (    tc2.copy_id = x.copy_id
            AND tc2.title_id = x.title_id)
WHEN MATCHED
THEN
   UPDATE SET tc2.status = 'RENTED'
           WHERE tc2.status = 'AVAILABLE';