CODEWITHSUNDEEP
sqloracle-database
kursk.ye
kursk.ye

Reputation: 549

all operator cause ORA-00936: missing expression

I get an error message when I execute follow statement:

UPDATE dept d 
SET city = ALL (SELECT city FROM locations l 
                WHERE d.location_id = l.location_id);

When I get rid of ALL operator, success.

UPDATE dept d 
SET city = (SELECT city FROM locations l 
            WHERE d.location_id = l.location_id);

Can someone tell me what the "ALL" operator is?

Upvotes: 1

Views: 606

Answers (1)

dani herrera
dani herrera

Reputation: 51715

ALL reserved word is for suqueries in select statements but not in UPDATE statement.

Quoting Subqueries with ALL Oracle Doc:

The word ALL, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns.”

That means that = sign should be use as a comparation, not as assignation.

If you expect that subquery resturns more than one row you can limit results with max or rownun:

UPDATE dept d 
SET city = (SELECT max( city ) FROM locations l 
            WHERE d.location_id = l.location_id);

Upvotes: 1

Related Questions