Update table using join- error ORA-00933

Question

I have a table need to update by joining condition with another table.

UPDATE a SET a.REF_ID = b.NEW_REF_ID FROM CRS_CUSTOMERS a INNER JOIN DAY0_SUBSET b ON a.CUSTOMER_ID = b.CURRENT_CUSTOMER_ID;

The above statement just complain ORA-00933: SQL command not properly ended, syntax error highlighted on the line FROM CRS_CUSTOMERS a

Answer 1

That's not valid UPDATE syntax.

Suggest you try something along the lines of [can't test it currently, and it assumes that ref_id and customer_id don't exist on the day0_subset table]

UPDATE CRS_CUSTOMERS
  SET REF_ID =
    (select b.NEW_REF_ID
       from DAY0_SUBSET b
       where b.current_customer_id = customer_id)
  where CUSTOMER_ID in (select b.CURRENT_CUSTOMER_ID from DAY0_SUBSET b)

Answer 2

You need a nested sub-select :

UPDATE CRS_CUSTOMERS a
 SET a.REF_ID = ( SELECT b.NEW_REF_ID
                   FROM DAY0_SUBSET b
                  WHERE a.CUSTOMER_ID = b.CURRENT_CUSTOMER_ID );

SQL Fiddle Demo