Updating multiple records in oracle

How can I change just the affected addresses in the contact database, assuming the username portion of their e-mail addresses remains the same?

UPDATE contacts.email ce SET email = ( SELECT REPLACE(email, '@osric.com', 'example.com') FROM contacts.email e INNER JOIN contacts.contact c ON e.contact_id = WHERE c.dept_id = 'oracle' AND e.email LIKE '%@osric.com' ) WHERE EXISTS ( SELECT 1 FROM contacts.email e INNER JOIN contacts.contact c ON e.contact_id = WHERE c.dept_id = 'oracle' AND e.email LIKE '%@osric.com' ); UPDATE contacts.email ce SET email = ( SELECT REPLACE(email, '@osric.com', '@example.com') FROM contacts.email e INNER JOIN contacts.contact c ON e.contact_id = WHERE c.dept_id = 'oracle' AND e.email LIKE '%@osric.com' AND ce.contact_id = e.contact_id ) WHERE EXISTS ( SELECT 1 FROM contacts.email e INNER JOIN contacts.contact c ON e.contact_id = WHERE c.dept_id = 'oracle' AND e.email LIKE '%@osric.com' AND ce.contact_id = e.contact_id ); That joins the rows from the parent UPDATE query with the rows from the SELECT subquery.

Then you can give the following insert statement Insert into emp (empno, ename, sal) select empno, ename, sal from old_emp; Suppose we have sales table with the following structure.

Sales Now we want to add the rows from SALES table Weekly_Sales Table in the following Structure.

Example use case: I have a database that contains a table of contacts (contact) and table of e-mail addresses (email), joined on = email.contact_id.

I just found out that Example Conglomerate acquired Osric Publishing’s Oracle consulting business, and so I need to update my contacts database so that all of the Oracle consultants who had @e-mail addresses now have @e-mail addresses.

These statements do not implicitly commit the current transaction. Manager’, ’8-aug-1998’,2000); If you want to add a new row by supplying values for some columns not all the columns then you have to mention the name of the columns in insert statements.

delete from emp; Use the MERGE statement to select rows from one table for update or insertion into another table. those rows which are already present in sales_history, their amount should be updated and those rows which are not present in sales_history table should be inserted.

The decision whether to update or insert into the target table is based on a condition in the ON clause. merge into sales_history sh using sales s on (s.prod=and s.month=sh.month) when matched then update set sh.amount=s.amount when not matched then insert values (prod,month,amount); After the statement is executed sales_history table will look like this.

For example We want to change the name of employee no 102 from ‘Sami’ to ‘Mohd Sami’ and to raise the salary by 10%. update emp set name=’Mohd Sami’, sal=sal (sal*10/100) where empno=102; Now we want to raise the salary of all employees by 5%.

update emp set sal=sal (sal*5/100); Now to change the names of all employees to uppercase.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating multiple records in oracle”