asked on

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 ?

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?

FutureDBA-

ASKER

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"

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FutureDBA-

ASKER

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.

FutureDBA-

ASKER

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

Thank you very much to both of you.

both were equally helpful