Home » SQL & PL/SQL » SQL & PL/SQL » Update Query (HP-UX, Oracle 9i)
icon1.gif  Update Query [message #288042] Fri, 14 December 2007 07:58 Go to next message
ritvikd
Messages: 11
Registered: January 2007
Junior Member
I had one table work_list having structure like
Quote:


Name Null? Type
----------------------------------------- -------- ----------------------------
FK_WL_ACTIVITY NOT NULL VARCHAR2(4)
FK_WL_VIEW_ACTIVITY NOT NULL VARCHAR2(4)
FK_OUN_ID NOT NULL NUMBER(6)
FK_REF NOT NULL NUMBER(10)
FK_NO NOT NULL NUMBER(2)


This table was updated following a PL/SQL query. This has incorrectly updated some cases
which shouldn't have been updated. All the updated cases were inserted into one temp table
name temp_work_list which has structure like

Quote:

Temp_Worklist
(REF number(10) not null,
No number(2) not null,
old_oun_id not null,
correct_oun_id not null)


I need to update work_list table again for old_oun_id having values (390000,390100).
I have written PL/SQL to do that, but I believe that one single Update statement is also possible.

The update statement I have written is

update work_list
set FK_LIS_OUN_ID = tmp.old_oun_id
where (FK_REF, fk_no,fk_oun_id) in
                            (select ref,no,correct_oun_id
			      from Temp_Worklist tmp
				where tmp.old_oun_id in       
                               (390000,390100));


I know this is incorrect as tmp.old_oun_id is invalid identifier. How can I re-write the query which will update work_list table.

The data in table work_list before initial PL/SQL query was applied was

Quote:

'ABCD','ABCV',300100,1234567899,2
'ABCD','ABCV',390000,5675678771,1
'ABCD','ABCV',390100,8563345734,5
'ABCD','ABCV',360100,2143774789,4

After PL/SQL work_list is

Quote:

'ABCD','ABCV',330100,1234567899,2
'ABCD','ABCV',370000,5675678771,1 - Incorrect Update
'ABCD','ABCV',381100,8563345734,5 - Incorrect Update
'ABCD','ABCV',380100,2143774789,4


and temp_work_list was inserted with records

Quote:

1234567899, 2, 300100, 330100
5675678771, 1, 390000, 370000
8563345734, 5, 390100, 381100
2143774789, 4, 360100, 380100



I hope this is clear.

Thanks,




Re: Update Query [message #288212 is a reply to message #288042] Sat, 15 December 2007 22:12 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at Updateable Join Views

Ross Leishman
Previous Topic: Different Types of reports
Next Topic: Fetch out of sequence error ....
Goto Forum:
  


Current Time: Sun Dec 04 12:49:20 CST 2016

Total time taken to generate the page: 0.16297 seconds