Update in Oracle

Question

I need to update the comments field in a table for a large list of customer_ids. The comment needs to be updated to include the existing comment and appending some text and the password which is in another table. I'm not quite sure how to do this.

Here is some code that does this for a single customer id. How would I go about doing this for a list of over 100 customer id's? my first thought is to make a temp table and place all the customer id's in there, but i'm still not sure how to code it after that since the customer_id is used in the query twice.

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'

Answer 1

Well, assuming Contract_comment has a customer_id, or is easily joined to a table that does have 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 subselect...

WHERE Customer_id in (select customer_id from ... ).

Answer 2

would you just use two separate joins to the temp table? one in the subquery and one in the outer query joining both on the customer_id in the temp table?

Answer 3

I agree with everything that Todd has stated, and wish to add the following.

Although it may not be obvious, he has also (likely) corrected another error from your posting, which is treating the customer_id field as a string.

By putting the id's inside quotes (at least in your example) you are forcing Oracle to either convert all of the rows to a string before comparing, or convert the ids you've provided to integers - I would have to experiment to determine the actual rules that would apply.

Either way, it is much better to be consistent with your data types and not leave it up to the database to figure it out.

You stated that the code provided was for a single customer. Ensure that if you deploy this code in a loop, that you use bind variables as opposed to just concatenating the customer id into the SQL statement that you are building. If you're doing this in PL/SQL then it's done for you - in Java or another language you would have to do this on your own.

Answer 4

Todd's answer above would work fine using an IN (or EXISTS clause if you're storing the IDs in a temp table. I'd just enhance it as follows:

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)

That ensures that 1) you're handling if the password is null and 2) that you only update for customers with a wlogin record.

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