Home » SQL & PL/SQL » SQL & PL/SQL » Need a Query to update....below are requirements
Need a Query to update....below are requirements [message #414061] Mon, 20 July 2009 04:17 Go to next message
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 #414066 is a reply to message #414061] Mon, 20 July 2009 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Need a Query to update....below are requirements [message #414067 is a reply to message #414061] Mon, 20 July 2009 04:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Something along the lines of
update xx_cc_audit
set contract_number = (query that returns correct contract no 
                       modified to reference the xx_cc_audit table) where contract_number is null
Re: Need a Query to update....below are requirements [message #414073 is a reply to message #414067] Mon, 20 July 2009 05:06 Go to previous messageGo to next message
sangakirankumar
Messages: 18
Registered: August 2008
Location: india
Junior Member

Hi ,

To Query , i need to pass the instance_id, but i'm unable to pass instance_id's for each null contracts in single query.

Please read my question thoroughly.

Thanks for reply
Re: Need a Query to update....below are requirements [message #414075 is a reply to message #414073] Mon, 20 July 2009 05:16 Go to previous messageGo to next message
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;
Re: Need a Query to update....below are requirements [message #414080 is a reply to message #414075] Mon, 20 July 2009 05:29 Go to previous message
sangakirankumar
Messages: 18
Registered: August 2008
Location: india
Junior Member

Hi JRowbottom,

Sorry and Thanks a lot...

This worked...But I need to check whether contract_numbers got updated with correct values.

Thanks again...
Previous Topic: Suggestiions on improving performance in this SQL (merged 3)
Next Topic: Error codes
Goto Forum:
  


Current Time: Wed Dec 07 04:52:59 CST 2016

Total time taken to generate the page: 0.10082 seconds