CODEWITHSUNDEEP
sqloracle-databasesql-update
lebowski
lebowski

Reputation: 1051

Trouble with Update-Set-From syntax in Oracle

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?

Upvotes: 2

Views: 92

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

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.

Upvotes: 3

Related Questions