Update statement on multiple joins

Posted on 2014-07-10
Last Modified: 2014-07-10
I am trying to update a table based on a join.
                                        o.USER_FIELD1||r.ROUTE_ID ERMS_ROUTE
                                        RS_ROUTE@RN r
                                                    LEFT OUTER  JOIN RS_STOP@RN     s ON s.ROUTE_PKEY = r.PKEY
                                                    LEFT OUTER  JOIN RS_ORDER@RN    o ON o.STOP_PKEY = s.PKEY
                                                    LEFT OUTER  JOIN TS_LOCATION@RN l ON l.ID = s.LOCATION_ID
                                                    LEFT OUTER  JOIN TS_EMPLOYEE@RN e ON e.ID = r.DRIVER1_ID
                                      WHERE r.RN_SESSION_PKEY = 26 and n.ORHORDNUM = o.ORDER_NUMBER AND ROUTE_ID <> 'Unassigned' AND ROUTE_ID <> 'LI');

Open in new window

I get an error saying that the field has a null value, but when i run the query by itself, (excluding n) i don't have any null values in  o.USER_FIELD1||r.ROUTE_ID ERMS_ROUTE

Error report -
SQL Error: ORA-01407: cannot update ("CDC"."RNORHP"."ORHRTENUM") to NULL
01407. 00000 -  "cannot update (%s) to NULL"

Open in new window

is my syntax wrong ?
Question by:FutureDBA-
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    There is no where clause on the update itself so it will try to update ALL rows in the table.

    I wonder if there is an ORHORDNUM in RNORHP that doesn't exist in RS_ORDER@RN?

    Author Comment

    slight, this could be a possibility, i only want to update records where they do exists. all other records can be left as is.

    Do i have to put the where clause outside the select statement?

    I'm not sure what you mean by " no where clause on the update itself"
    LVL 34

    Assisted Solution

    I agree with slightwv.

    Now, where to put your additional "where" clause to prevent this problem?  Yes, that has to be on the update.  You will have to add something like this to the end of your current statement:

    where exists (select 1 from RS_ROUTE@RN r  [add your join coditions but WITHOUT an outer join here]

    or, where rn.[key_field] in (select ... from from RS_ROUTE@RN r  [add your join coditions but WITHOUT an outer join here]

    Your next issue will likely be the slow performance of this update that has to do selects across a database link.  Database links are always *MUCH* slower than if the data is inside the database.  Also, if you do have to use a database link, try to get a view created in the remote database that does the joins you need, then select from just that one view over the link.  When you reference multiple tables over the link and join them locally, you may be waiting a *LONG* time for all of that data fetching and joining in your local TEMP tablespace to happen, before Oracle can actually use the results to do the update.
    LVL 76

    Accepted Solution

    >>I'm not sure what you mean by " no where clause on the update itself"

    I think Mark showed you but I want to explain it.

    What you basically have is:
    update sometable set some_column=some_value;

    The "some_value" is coming from a sub-select but that makes no difference to the update itself.  The update statement needs a where clause to 'limit' the rows actually being updated.

    The where clause inside the select doesn't count.

    For things like this, I prefer the MERGE command:

    There is a LOT more control over what happens and when.  There are a TON of examples out there if the one in the doc link doesn't help.

    If you want a cut/paste solution you will need to provide:
    Table definitions.
    Sample data.
    Expected results after the update/merge.

    Author Comment

    markgeer / slightwv

    The 2 databases are hosted on the same subnet, they are linked via a 10GB link on the same switch, full ssd. they are both Oracle 11gR2.

    When i run the select query by itself it's as fast as if i was selecting from the local schema im connected to.

    will i still get hit with performance issues regardless?

    I created the view as suggested on the remote db

    This is where I am at, based on the the documentation slightwv posted.

    	USING (SELECT order_number, route_id, sequence_number
    		from apex_sessiond@rn) a
    	on (a.order_number = r.orhordnum)
    WHEN MATCHED THEN update set r.orhrtenum = a.route_id

    Open in new window

    I am still getting
    Error report -
    SQL Error: ORA-01722: invalid number
    01722. 00000 -  "invalid number"

    Open in new window

    Thank you both for your replies.

    Author Comment

    that actually worked, once i filtered out a route type.

    Thank you very much to both of you.

    both were equally helpful

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to recover a database from a user managed backup

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now