0

I want to run a query like:

UPDATE   I
SET I.CAP_NAME =  S.CAP_NAME 
FROM INSURED_FARMERS  I 
INNER JOIN INDIAN_STATES_REGION  S 
   ON UPPER(I.STATE_NAME_FI) = UPPER(S.STATE_NAME)

but it throws an error: sql error ora-00933 sql command not properly ended on line: SET I.CAP_NAME = S.CAP_NAME

Arion
  • 30,443
  • 10
  • 68
  • 86
user3168102
  • 1

2 Answers2

1

I don't think you can write an UPDATE statement in Oracle using a JOIN.

UPDATE INSURED_FARMERS I
SET I.CAP_NAME = ( SELECT S.CAP_NAME 
                   FROM INDIAN_STATES_REGION  S 
                   WHERE UPPER(I.STATE_NAME_FI) = UPPER(S.STATE_NAME) )
WHERE EXISTS ( SELECT S.CAP_NAME 
               FROM INDIAN_STATES_REGION  S 
               WHERE UPPER(I.STATE_NAME_FI ) = UPPER(S.STATE_NAME) )
Drumbeg
  • 1,853
  • 1
  • 14
  • 22
  • 1
    Since you're faster...http://sqlfiddle.com/#!4/60d85/1 – Passerby Jan 07 '14 at 07:27
  • Well pointed out @Passerby that there are two approaches to this. i.e. Dropping the `EXISTS` would result in setting `NULL` for all records without a match. – Drumbeg Jan 07 '14 at 07:33
1

see here: stackoverflow

you can do both ways:

  1. like there is an answer with exists
  2. using inline view if your tables are connected by the columns with unique indexes or primary keys (these columns UPPER(I.STATE_NAME_FI) = UPPER(S.STATE_NAME))
Community
  • 1
  • 1
smnbbrv
  • 21,588
  • 9
  • 69
  • 105