Update multiple columns with function returning multiple values

Question

I'm trying to update a table with the multiple returns of a function.

I have created a TYPE

CREATE OR REPLACE TYPE city_state AS OBJECT
(
  city VARCHAR2(30),
  state VARCHAR2(2)
);
/

and I have a function that returns a variable of this type.

CREATE OR REPLACE FUNCTION closestcity(lat IN NUMBER, lon IN NUMBER) RETURN city_state IS
...

I need to update a table that contains city, state, latitude and longitude columns. With the lat/lon, I should call the function and use the result to update the value of the city/state. I only want to call the function once for each row and there are only some rows that I need to update (let's say city is NULL)

This is what I got so far

UPDATE (SELECT * FROM t t1 WHERE city IS NULL)
SET (city, state) = (
                     SELECT newcity.city, newcity.state FROM
                       ( SELECT closestcity(latitude, longitude) newcity
                         FROM t t2
                         WHERE t1.latitude = t2.latitude AND
                               t1.longitude = t2.longitude)
                    );

but I get an invalid identifir error. I feel like I'm overcomplicating it, what would be the correct approach to this?

Community · Accepted Answer · 2017-05-23 11:48:56Z

2

The reason you've got that error is because Oracle attempts to qualify newcity referenced in the SELECT newcity.city, newcity.state select statement. It cannot find the object newcity and thus throws the error. Also alias the inline view as t1 if you really need it. As @Rene has mentioned, you can successfully replace it with the table name. To that end you may rewrite your update statement as follows:

UPDATE (SELECT * FROM t t1 WHERE city IS NULL) t1
   SET (city, state) = (
                         SELECT closestcity(latitude, longitude).city 
                              , closestcity(latitude, longitude).state
                           FROM t t2
                          WHERE t1.latitude = t2.latitude 
                            AND t1.longitude = t2.longitude 
                        );

Or simply:

UPDATE t t1 
   SET city  = closestcity(latitude, longitude).city 
     , state = closestcity(latitude, longitude).state
where t1.city is null

Update#1

update (select nd.newdata.city  as newcity
             , nd.newdata.state as newstate
             , city
             , state
          from (                             
                 SELECT closestcity(latitude, longitude) newdata
                      , city
                      , state
                   FROM t
                  where city is null
                ) nd
              ) q  
set q.city  = q.newcity
  , q.state = q.newstate

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Nov 27, 2012 at 6:35

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iomartin Over a year ago

This works, but I end up calling closestcity twice - and it's a very heavy function

Rene · Accepted Answer · 2012-11-27 06:35:16Z

0

The syntax should be:

UPDATE <table1>
set (<column1>, <column2>) = (select <column1>,<column2> 
                              from <table2> 
                              where <where clause table2 > )
where <where clause table1>

So your query should be:

UPDATE t1
SET (city, state) = (SELECT closestcity(latitude, longitude) newcity
                            ,state
                       FROM t t2
                      WHERE t1.latitude = t2.latitude 
                        AND t1.longitude = t2.longitude)
where city IS NULL

answered Nov 27, 2012 at 6:35

Rene

10.6k5 gold badges37 silver badges48 bronze badges

1 Comment

iomartin Over a year ago

state is a variable of newcity (just like city is), I can't select it directly

Nick Krasnov · Accepted Answer · 2012-11-27 07:07:25Z

0

Just one change

UPDATE (SELECT * FROM t WHERE city IS NULL) **t1**
SET (city, state) = (
                     SELECT newcity.city, newcity.state FROM
                       ( SELECT closestcity(latitude, longitude) newcity
                         FROM t t2
                         WHERE t1.latitude = t2.latitude AND
                               t1.longitude = t2.longitude)
                    );

edited Nov 27, 2012 at 7:07

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

answered Nov 27, 2012 at 6:47

Sunil Khatri

462 bronze badges

1 Comment

iomartin Over a year ago

I get ORA-00904: "T1"."LONGITUDE": invalid identifier

Collectives™ on Stack Overflow

Update multiple columns with function returning multiple values

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related