[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

Update statement on multiple joins

I am trying to update a table based on a join.
UPDATE RNORHP n SET n.ORHRTENUM = (SELECT 
                                        o.USER_FIELD1||r.ROUTE_ID ERMS_ROUTE
                                      FROM 
                                        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"
*Cause:    
*Action:

Open in new window


is my syntax wrong ?
0
FutureDBA-
Asked:
FutureDBA-
  • 3
  • 2
2 Solutions
 
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?
0
 
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"
0
 
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.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

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.
0
 
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.

MERGE INTO RNORHP r
	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"
*Cause:    
*Action:

Open in new window



Thank you both for your replies.
0
 
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now