2

I am trying to execute a simple SQL update-set-from statement in Oracle, which looks like the one below:

update
table1 t1
set
t1.col1=t2.col1
from
(select distinct t1.col1 from table1 t1 where t1.col2='xxx') as t2
where
t1.col1='yyy';

I haven't used from with update-set before, but this syntax looks okay to me. Yet it fails with this error:

Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action:

My desired result is for the inner select to return a single record, which then gets inserted/updated into table1's col1.

Also, should I be using a different alias thant1 for table1 in the inner select statement, considering that I have already used t1 in the update statement?

lebowski
  • 1,031
  • 2
  • 20
  • 37
  • 1
    You have mixed up something - there is no such option in Oracle database as `UPDATE ... FROM ...`, please study the [Oracle's syntax of UPDATE](https://docs.oracle.com/database/121/SQLRF/statements_10008.htm#SQLRF01708) – krokodilko Apr 11 '18 at 19:55

1 Answers1

3

Syntax should be

update table t1 set
  t1.col1 = (select distinct t2.col1 
             from table1 t2
             where t2.col2 = 'xxx')
where t1.col1 = 'yyy';

Note that DISTINCT doesn't necessarily mean that SELECT will return a single value; if it does not, you'll end up in TOO-MANY-ROWS error.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57