Home » SQL & PL/SQL » SQL & PL/SQL » Update - blanks existing values for columns
Update - blanks existing values for columns [message #265777] Fri, 07 September 2007 06:24 Go to next message
raj75
Messages: 11
Registered: August 2007
Junior Member
UPDATE METRO_FDC.E_L_PROC a
SET (a.EQU_IP, a.TRAV_IP, a.INSERT_DT)= ( 
SELECT b.EQU_IP, b.TRAV_IP, b.INSERT_DT 
FROM TEMP_L_PROC b
WHERE a.LOTO = b.LOTO and
			a.STEP = b.STEP);


After executing the above code, I queried metro_fdc.e_l_proc and the value seems to be blank for the 3 columns while previously it existed.

select * from metro_fdc.e_l_proc;

LOTO STEP EQU_IP TRAV_IP INSERT_DT
-------------------- -------------------- --------- ---------- ---------
6089857.006 4000-41 CONTACT BPSG DEP
6089857.001 4000-41 CONTACT BPSG DEP AAAAAAA100 U48BJ72500 07-SEP-07
6089857.002 4000-41 CONTACT BPSG DEP BPPR7A6100 U48BK72500 07-SEP-07
6089857.003 4000-41 CONTACT BPSG DEP CCCCCC5200 U48BJ72500 07-SEP-07
6089857.004 4000-41 CONTACT BPSG DEP BPPR7A6100 U48BK72500 07-SEP-07
6089857.005 4000-41 CONTACT BPSG DEP BPPR7A6200 U48BD11111 07-SEP-07
Re: Update - blanks existing values for columns [message #265778 is a reply to message #265777] Fri, 07 September 2007 06:25 Go to previous messageGo to next message
raj75
Messages: 11
Registered: August 2007
Junior Member
Kindly address if this can be fixed as the code run on Oracl 9.2.0.7. Thanks
Re: Update - blanks existing values for columns [message #265783 is a reply to message #265778] Fri, 07 September 2007 06:58 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are updating ALL records in the 'e_l_proc' table, so - those that don't have a new value set from a SELECT statement are set to NULL.

Include additional WHERE clause for the UPDATE statement, something like
UPDATE E_L_PROC a SET 
  (a.EQU_IP, a.TRAV_IP, a.INSERT_DT) = 
  (SELECT b.EQU_IP, b.TRAV_IP, b.INSERT_DT 
   FROM TEMP_L_PROC b
   WHERE a.LOTO = b.LOTO 
     AND a.STEP = b.STEP
  )
WHERE EXISTS (SELECT NULL
               FROM TEMP_L_PROC b
               WHERE a.LOTO = b.LOTO 
                 AND a.STEP = b.STEP
              );
This might (or might not) do the job; see what condition must be met in order to update only desired records.
Re: Update - blanks existing values for columns [message #267945 is a reply to message #265777] Sun, 16 September 2007 20:59 Go to previous messageGo to next message
raj75
Messages: 11
Registered: August 2007
Junior Member
Thanks I was able to use that to fix the blanks with the additional query however I dont understand why this statement is required when the previous condition is supposed to do the job.

Re: Update - blanks existing values for columns [message #267971 is a reply to message #267945] Mon, 17 September 2007 00:28 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Simply because the previous condition is not supposed to do the job. Your query updated the WHOLE table, not only those that have matching records in both 'e_l_proc' and 'temp_l_proc' tables. Values in all those "unmatching" records were set to NULL.
Re: Update - blanks existing values for columns [message #268068 is a reply to message #265777] Mon, 17 September 2007 07:45 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
To try and explain further:

The select within your update statement is choosing which values to use to update the appropriate columns.

The where clause outside the inline select is choosing which rows in the table should be updated. Without this specification all the rows in the table will be updated, hence your null values.

[Updated on: Mon, 17 September 2007 07:45]

Report message to a moderator

Re: Update - blanks existing values for columns [message #269716 is a reply to message #265777] Mon, 24 September 2007 05:06 Go to previous message
raj75
Messages: 11
Registered: August 2007
Junior Member
Thanks a lot guys for your valuable inputs.
Previous Topic: Missing Comma
Next Topic: Error in Running Parallel Queries
Goto Forum:
  


Current Time: Wed Dec 07 16:21:14 CST 2016

Total time taken to generate the page: 0.06964 seconds