Update multiple values in an oracle table using values from an APEX collection

Question

I am using APEX collections to store some values and pass them between pages in Oracle Application Express 4.2.3.

I would like to then perform an update statement on a table called "project" with the values from the collection.

My code so far is as follows:

 update project
  SET name=c.c002, 
      description=c.c007, 
      start_date=c.c004, 
      timeframe=c.c005, 
      status=c.c009
  FROM 
      apex_collections c
  WHERE 
      c.collection_name = 'PROJECT_DETAILS_COLLECTION'
      and id = :p14_id;

where :p14_id is the value of a page item.

However, I am getting the following error:

ORA-00933: SQL command not properly ended

Anyone have any idea on how to approach this?

Thanks!

Answer 1

The UPDATE syntax you are using is not valid in Oracle; it does not allow you to use FROM in the way you are attempting.

The simplest way to do this in Oracle would with a subquery:

update project
  set (name, description, start_date, timeframe, status) = 
    (select c.c002, c.c007, c.c004, c.c005, c.c009
       FROM 
         apex_collections c
       WHERE 
         c.collection_name = 'PROJECT_DETAILS_COLLECTION'
    )
  WHERE
    id = :p14_id
  ;

Note that if the subquery returns no rows, the columns in the target table will be updated to NULL; this could be avoided by adding a similar EXISTS condition in the predicate for the update. It could also be avoided by using a MERGE statement instead of an UPDATE.

If the subquery returns multiple rows, the statement will throw an error. It looks like tthat should not be the case here.