Oracle update
I need to update a comment field in a table for a large list of customer_ids. The comment should be updated to include the existing comment and add the text and password that is in another table. I'm not really sure how to do this.
Here's the code that does it for one customer ID. How can I do this for a list of more than 100 customer IDs? my first thought is to create a temporary table and put the entire client id in there, but I'm still not sure how to encode it after that as the client_id is used twice in the request.
Update Contract
SET Contract_Comment= Contract_Comment || '; 12/29/2008 Password `' ||
(SELECT Password FROM WLogin WHERE default_customer_id='1234' ) ||'`'|| ' reinstated per Mickey Mouse;'
WHERE Customer_id='1234'
Well, assuming Contract_comment has a client_id, or easily joins to a table that has one ...
update contract c
set contract_comment = contract_comment || '; 12/29/2008 Password ' ||
(select password from WLogin w where w.default_customer_id = c.customer_id) ||''|| ' reinstated per Mickey Mouse;' WHERE Customer_id in (1234, 4567).
Or replace the list at the end with another subquery ...
WHERE Customer_id to (select customer_id from ...).
I agree with everything Todd said and would like to add the following.
While it may not be obvious, he also (most likely) fixed another bug from your post that treats the customer_id field as a string.
By placing the identifier inside quotes (at least in your example), you are forcing Oracle to either convert all strings to a string before comparing, or convert the identifiers that you provided to integers - I would have to experiment with determining the actual rules that would apply.
In any case, it is much better to be compatible with your datatypes and not leave it in the database to figure it out.
You stated that the code is provided for one client. Make sure that when you deploy this code in a loop, bind variables are used and not just concatenate the client ID into the SQL statement you are building. If you do it in PL / SQL, then it is done for you - in Java or another language, you have to do it yourself.
source to share
Todd's answer above will work fine using the IN clause (or EXISTS if you store IDs in a temp table. I would just improve it like this:
UPDATE contract c
SET contract_comment = nvl2(contract_comment, contract_comment || '; ', '') || '12/29/2008 Password ' ||
NVL((SELECT PASSWORD
FROM wlogin p
WHERE p.default_customer_id = c.customer_id),
'<NULL>') || '' || ' reinstated per Mickey Mouse'
WHERE EXISTS (SELECT 'x' FROM wlogin l WHERE l.default_customer_id = c.customer_id)
This ensures that 1) you work if the password is zero, and 2) that you only update for clients with a wlogin entry.
Feel free to add something like:
AND EXISTS (SELECT 'y' FROM temp_ids_table t WHERE t.customer_id = c.customer_id)
Thanks for any credit you give for these correct answers.
- Stew
source to share