Forum Stats

  • 3,759,178 Users
  • 2,251,510 Discussions
  • 7,870,526 Comments

Discussions

Updating multiple rows with calculated values (distance by Pythagorean theorem)

1ddb0fc1-fc76-4548-a8f5-708fc37d36a1
edited Dec 11, 2014 9:21PM in SQL & PL/SQL

Updating multiple rows with calculated values (distance by Pythagorean theorem)

Very confused been sat in front of this screen for 6 hours straight trying to figure this out -  how do I update all rows with calculated data derived from the values in two other columns of the same record.

here's the situation:

I have a table called 'customers'. It had 4 columns - 'Customer_ID', 'Customer_Name', ' Coordinate_X' and 'Coordinate_Y'. since creating and filling the table with data I have added an empty 5th column called 'Distance'

What I'd like to do is fill the 'distance' column with the calculated distances from (0,0) to the locations specified in the 'Coordinate_X' and 'Coordinate_Y' columns of each row. I figured I could use Pythagoras's theory - and the correct values come up when I use the query:

Select SQRT(Power(customers.coordinate_x,2) + Power(customers.coordinate_y,2)) from customers

I fiddled about trying to make a trigger - failed that through lack of experience.

messed about trying to create a vie - failed that

Then I tried to fill the column using queries - failed that too.

I've been trying things like this;

update customers set distance = (Select SQRT(Power(customers.coordinate_x,2) + Power(customers.coordinate_y,2)) from customers)

Nothing's working - the best I'm getting is ORA-01427: single-row subquery returns more than one row

Any ideas?

Kind regards,

Neil

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    edited Dec 11, 2014 8:52PM

    Hi, Neil,

    Welcome to the forum!

    You almost had it!  You just don't need the query.  All you need is:

    UPDATE  customers
    SET     distance = SQRT ( Power (coordinate_x, 2)
                            + Power (coordinate_y, 2)
                            )
    ;
    
    
    
    

    A query is needed when you need to get values from other rows.  In this case, all the data you need to update each row is already on the row itself, so there's no need for any query.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    edited Dec 11, 2014 8:59PM

    Hi,

    Whenever you have a problem, it really helps if you post a little sample data (CREATE TABLE and INSERT statements) to let the people who want to help you re-create the problem and test their ideas.  Include any special cases you need to test (e.g., negative coordinates or NULLs), like this:

    CREATE TABLE  customers
    (   Customer_ID        NUMBER (8)  PRIMARY KEY
    --,   Customer_Name    VARCHAR2 (10)  -- plays no part in this problem
    ,   Coordinate_X       NUMBER
    ,   Coordinate_Y       NUMBER
    ,   Distance           NUMBER
    );
    
    
    INSERT INTO customers (customer_id, coordinate_x, coordinate_y) VALUES (0,   0,   0);
    INSERT INTO customers (customer_id, coordinate_x, coordinate_y) VALUES (1,  30,  40);
    INSERT INTO customers (customer_id, coordinate_x, coordinate_y) VALUES (2, -10, -10);
    INSERT INTO customers (customer_id, coordinate_x, coordinate_y) VALUES (3,  12,   5);
    INSERT INTO customers (customer_id, coordinate_x, coordinate_y) VALUES (4,   5   12);
    INSERT INTO customers (customer_id, coordinate_x, coordinate_y) VALUES (9,  10, NULL);
    COMMIT;
    
    
    
    

    Also post the exact results you want from that sample data.  In a problem involving DML, such as UPDATE, "results" means the contents of the changed table after everything is finished.  In this case:

        CUSTOMER_ID    COORDINATE_X    COORDINATE_Y        DISTANCE
    --------------- --------------- --------------- ---------------
                  0               0               0               0
                  1              30              40              50
                  2             -10             -10 14.142135623731
                  3              12               5              13
                  9              10
    
    
    
    

    Always say which version of Oracle you're using (e.g., 11.2.0.3.0).  In this case, it doesn't matter (the statement in reply #1 is good in any version), but it's easy to post, and sometimes the version makes a big difference.

    See the Forum FAQ:

    Using this sample data, I was able to test the UPDATE statement in reply #1, and it popuates the distance column as shown above.

  • Unknown
    edited Dec 11, 2014 9:21PM
    Updating multiple rows with calculated values (distance by Pythagorean theorem)
    
    Very confused been sat in front of this screen for 6 hours straight trying to figure this out -  how do I update all rows with calculated data derived from the values in two other columns of the same record.
    
    here's the situation:
    
    I have a table called 'customers'. It had 4 columns - 'Customer_ID', 'Customer_Name', ' Coordinate_X' and 'Coordinate_Y'. since creating and filling the table with data I have added an empty 5th column called 'Distance'
    
    What I'd like to do is fill the 'distance' column with the calculated distances from (0,0) to the locations specified in the 'Coordinate_X' and 'Coordinate_Y' columns of each row. I figured I could use Pythagoras's theory - and the correct values come up when I use the query:
    
     

    If you are using 11g just add a VIRTUAL column - you don't need a real column that holds real data and takes real space.

    You can query the virtual column and Oracle will do that computation for you on-the-fly.

    Use the formula the other responder provided.

    See the Oracle docs for an example of a VIRTUAL column

    https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#BABIDDJJ

    Adding a Virtual Table Column: Example 
    The following statement adds to a copy of the hr.employees table a column named income, which is a combination of salary plus commission. Both salary and commission are NUMBER columns, so the database creates the virtual column as a NUMBER column even though the datatype is not specified in the statement: CREATE TABLE emp2 AS SELECT * FROM employees; ALTER TABLE emp2 ADD (income AS (salary + (salary*commission_pct)));
This discussion has been closed.