Oracle UPDATE using SELECT statement that includes JOIN

Question

I'm porting an H2 UPDATE/SELECT statement to Oracle, and am running into a syntax issue that I'm having trouble diagnosing.

I have 3 tables, Users, Cars, and Trips. They look like:

SELECT * FROM USERS;
+------------+----+--------+
|  USERNAME  | ID | CAR_ID |
+------------+----+--------+
| John.Smith |  1 |     5  |
| Abby.Smith |  2 |     6  |
+------------+----+--------+

SELECT * FROM CARS;
+----+--------+-------+
| ID |  MAKE  | COLOR |
+----+--------+-------+
|  5 | Subaru | Green |
|  6 | Honda  | Red   |
+----+--------+-------+

SELECT * FROM TRIPS;
+----+------------+---------+
| ID |  USERNAME  | MILEAGE |
+----+------------+---------+
|  8 | Abby.Smith |      87 |
|  9 | John.Smith |      23 |
+----+------------+---------+

However, we have now decided to add a new column to Trips, and keep track of the car model (only) instead of the user. As an intermediate result, before dropping the USERNAME column, I'm trying to achieve:

+----+------------+---------+-----------+
| ID |  USERNAME  | MILEAGE | CAR_MODEL |
+----+------------+---------+-----------+
|  8 | Abby.Smith |      87 |     Honda |
|  9 | John.Smith |      23 |    Subaru |
+----+------------+---------+-----------+

And my (non-functional) statement looks like:

WITH USER_MODELS AS
(SELECT USERNAME, MAKE FROM USERS JOIN CARS ON (USERS.CAR_ID = CARS.ID))
UPDATE TRIPS SET CAR_MODEL =
    SELECT MAKE FROM USER_MODELS
    WHERE TRIPS.USERNAME = USER_MODELS.USERNAME

However, the syntax error I receive is:

UPDATE TRIPS SET CAR_MODEL =
*
ERROR at line 3:
ORA-00928: missing SELECT keyword

Which is throwing me for a loop. The SELECT statement is immediately following the UPDATE, which is itself preceded by a WITH clause that seems to include a properly-formatted SELECT.

Solution

It can be done, but you need to put the WITH clause inside the subquery:

UPDATE TRIPS SET CAR_MODEL =
( WITH USER_MODELS AS
       (SELECT USERNAME, MAKE FROM USERS JOIN CARS ON (USERS.CAR_ID = CARS.ID))
  SELECT MAKE FROM USER_MODELS
   WHERE TRIPS.USERNAME = USER_MODELS.USERNAME
);