-1

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

user2102665
  • 429
  • 2
  • 11
  • 26

2 Answers2

0

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)
MandyShaw
  • 1,088
  • 3
  • 14
  • 22
0

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • your script is correct, but i wonder it will will all records in CRS_CUSTOMERS. For example, if i just need to REF_ID of a particular CUSTOMER ID? – user2102665 Apr 16 '18 at 11:11
  • @user2102665 not all records, but an individual record, because of `WHERE a.CUSTOMER_ID =`. Update for only matching `a.CUSTOMER_ID = b.CURRENT_CUSTOMER_ID`, individually. – Barbaros Özhan Apr 16 '18 at 11:37