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!

incorrect syntax using "from" in such way read [this](http://stackoverflow.com/questions/7030699/oracle-sql-update-with-data-from-another-table) — Dmitry.Samborskyi, Jun 10 '15 at 13:42

score 2 · Accepted Answer · answered Jun 10 '15 at 13:45

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.

Got it! Thanks for your answer! – Rawan Khalil Jun 11 '15 at 14:34 — Rawan Khalil, Jun 11 '15 at 14:34

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

1 Answers1