Update statement on multiple joins

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 ?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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?
FutureDBA-Author Commented:
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"
Mark GeerlingsDatabase AdministratorCommented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FutureDBA-Author Commented:
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.
FutureDBA-Author Commented:
that actually worked, once i filtered out a route type.

Thank you very much to both of you.

both were equally helpful
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.