Need a Query to update....below are requirements [message #414061] |
Mon, 20 July 2009 04:17  |
sangakirankumar
Messages: 18 Registered: August 2008 Location: india
|
Junior Member |

|
|
Hi,
I need a query to update table xx_cc_audit. below are requirements.
In xx_cc_audit I have more than 1000 rows. In this table Column - contract_number is inserted as null for some rows.
I need a query to update this xx_cc_audit table which will replace null contract numbers with correct ones.
In table xx_cc_audit I have column - instance_id which is not null and not unique.(duplicate instance id's can be there).
With this instance_id i can get the contract_number from other table okc_k_headers_b .
I need a query to update xx_cc_audit table.
Presently what I'm doing :
Select XS.instance_id
FROM cs_incidents_all_b CS,xx_cc_audit XS
where XS.contract_number is null
AND XS.SR_NUMBER = CS.incident_number
AND XS.ACTIVITY_TYPE = 'TROUBLESHOOTING';
Note : The above query can give duplicate instance_id.
Say, above query results in 100rows.
I will copy any 1 instance_id in a paper and then pass it to below query.
SELECT contract_number
FROM okc_k_headers_b
WHERE id = (SELECT MAX(dnz_chr_id)
FROM okc_k_items
WHERE object1_id1 = TO_CHAR(144849056) --instance_id
AND jtot_object1_code = 'OKX_CUSTPROD'
AND object1_id2 = '#'
AND chr_id IS NOT NULL);
I'll get contract_number.
then i'll update xx_cc_audit table as below.
update xx_cc_audit set contract_number = 1223464789 -- which i got as a result of above query
where contract_number is null
and instance_id = 144849056 --this i copied from paper, as mentioned above ;
this will update my table.
I have more than 1000rows, so i need a query which will update 1000rows in 1 shot.
Please help me.
Thanks!
|
|
|
|
|
|
Re: Need a Query to update....below are requirements [message #414075 is a reply to message #414073] |
Mon, 20 July 2009 05:16   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I have read your query quite thoroughly. I would suggest that you have failed to understand my suggestion.
If you'd bothered to supply create tables and insert statements for a test case, we could have produced a tested solution.
As it is, something along this line is what I mean:update xx_cc_audit tab
set contract_number = (SELECT contract_number
FROM okc_k_headers_b
WHERE id = (SELECT MAX(dnz_chr_id)
FROM okc_k_items
WHERE object1_id1 = TO_CHAR(tab.instance_id) --instance_id
AND jtot_object1_code = 'OKX_CUSTPROD'
AND object1_id2 = '#'
AND chr_id IS NOT NULL)) -- which i got as a result of above query
where contract_number is null;
|
|
|
|