Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle SQL intro #8

DML statements
by

Rodion Mironov

on 10 April 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #8

INSERT/UPDATE/DELETE
Conditions based on other table
Merge
Multi table insert
Truncate Lesson agenda Introduction to Oracle SQL.
DML statements. INSERT statement INSERT INTO <table> [(column [, column...])] insert into regions
values (5,'Australia') With columns list:
number of values should match number of columns listed INSERT INTO countries(country_id, country_name, region_id)
VALUES ('SL', 'Sealand', 1) Column not listed in insert statement:
will be assigned default values (if defined);
otherwise, will be assigned NULL (if allowed);
otherwise, insert statement will fail. INSERT INTO countries(country_name)
VALUES ('Neverland') SELECT <column> [,<column>]
FROM INSERT INTO contacts(
first_name
,last_name
,email
,phone)
SELECT
first_name
,last_name
,NVL(email, 'No email')
,NVL(phone_number,'No phone')
FROM employees
WHERE email is not null
or phone_number is not null UPDATE statement UPDATE <table>
SET <column> = <value> [, column = value, ...]
[WHERE condition]; UPDATE employees
SET salary = salary*1.1 UPDATE employees
SET commission_pct=commission_pct*2
WHERE hire_date < SYSDATE - 5*365 UPDATE employees e
SET salary = (select j.max_salary
from jobs j
where j.job_id = e.job_id); UPDATE employees
SET (salary,commission_pct) = (select salary, commission_pct
from employees
where employee_id = 145)
WHERE employee_id = 146 DELETE [FROM] <table>
[WHERE condition]; DELETE FROM job_history; DELETE FROM jobs j
WHERE not exists (select null
from employees e
where j.job_id = e.job_id)
and not exists (select null
from job_history jh
where jh.job_id = j.job_id) TRUNCATE statement:
removes all rows from table;
does it "fast";
is DDL (commits previous transaction and can not be rolled back). VALUES(value [, value...]) Single-row insert: Multi-row insert: INSERT INTO regions VALUES (5,'Australia') INSERT INTO without columns list:
number of values should match number of columns in table;
order of values should match order of columns in table;
dangerous in production code. -- column 1 -- value 1 -- column 4 -- value 4 The simplest UPDATE: With WHERE condition: Based on other table: Based on other table (several columns): -- REMEMBER!
-- subquery returning no rows
-- is NULL DELETE statement Multi-table INSERT create table job_history_2005 as select * from job_history where 1=0;
create table job_history_2006 as select * from job_history where 1=0;
create table job_history_2007 as select * from job_history where 1=0;
create table job_history_old as select * from job_history where 1=0;

INSERT FIRST
WHEN extract(year from end_date)=2005 THEN
INTO job_history_2005 (employee_id, start_date, end_date, job_id, department_id)
VALUES (employee_id, start_date, end_date, job_id, department_id)
WHEN extract(year from end_date)=2006 THEN
INTO job_history_2006 (employee_id, start_date, end_date, job_id, department_id)
VALUES (employee_id, start_date, end_date, job_id, department_id)
WHEN extract(year from end_date)=2007 THEN
INTO job_history_2007 (employee_id, start_date, end_date, job_id, department_id)
VALUES (employee_id, start_date, end_date, job_id, department_id)
ELSE
INTO job_history_old (employee_id, start_date, end_date, job_id, department_id)
VALUES (employee_id, start_date, end_date, job_id, department_id)
SELECT employee_id, start_date, end_date, job_id, department_id
FROM job_history; 3 multi-table insert options:
insert into just ONE table (INSERT FIRST...WHEN..., shown example);
insert into SEVERAL tables (INSERT ALL...WHEN...).
unconditional insert into ALL tables (INSERT ALL without WHEN). TRUNCATE TABLE job_history_2005; ` Useful links http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#i2163698 Insert http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10008.htm#i2067715 Update http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8005.htm#i2117787 Delete http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#i2081218 Merge Practice 1.Add yourself to EMPLOYEES table.

2.Update all emails in EMPLOYEES table adding "@epam.com" to the end.

3.Set all employees’ salary to maximum salary available for their position (see JOBS table).

4.Delete records with even EMPLOYEE_ID from JOB_HISTORY table.

5.Delete current records from JOB_HISTORY table. Record is considered "current" if combination of (employee_id, start_date, job_id and department_id) is present in EMPLOYEES table.

6.Modify you own record in EMPLOYESS (added in Task #1) to have NULL in DEPARTMENT_ID.

7.Modify "MERGE INTO emp_directory" statement shown in class. For employees without department DEPARTMENT_ID and CITY fields should be set to 'Unknown' in both insert and update part of statement. Execute MERGE statement and ensure it works correctly.







-- Quite simple.
-- But with limitations for subquery
-- (no GROUP BY, no DISTINCT, key-preserved table rule etc.)
-- MERGE is a good alternative.
update (
select e.hire_date as old_date, fd.first_day as new_date
from employees e
inner join first_days fd on e.employee_id = fd.employee_id)
set old_date = new_date; Syntax options for UPDATE from other table. AskTom. Update from other table. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:43440209618042 MERGE statement Typical SQL task:
table with some [outdated] data;
source of new data (table, view, query);
we should update outdated info: insert new rows, update existing rows. MERGE INTO <table> [alias]
USING <table> | <view>|<subquery>
ON <condition>
WHEN MATCHED
THEN UPDATE SET column1 = value1 [, column2=value2,...]
WHEN NOT MATCHED
THEN INSERT [(column1 [,column2,...) ] VALUES value1 [,value2...] create table regions_new as select * from regions;

insert into regions_new(region_id, region_name) values (5,'Australia');
update regions_new set region_name = 'United Europe'
where region_id = 1;


MERGE INTO Regions r
USING regions_new rn ON (r.region_id = rn.region_id)
WHEN MATCHED THEN UPDATE SET
r.region_name = rn.region_name
WHEN NOT MATCHED THEN INSERT (region_id, region_name)
VALUES (rn.region_id, rn.region_name); drop table emp_directory;
create table emp_directory as
select
e.employee_id
,e.first_name
,e.last_name
,e.email
,e.phone_number
,j.job_title
,d.department_name
,l.city
from employees e
inner join jobs j on e.job_id = j.job_id
left outer join departments d on e.department_id = d.department_id
left outer join locations l on d.location_id = l.location_id;

select * from emp_directory;

update employees set
phone_number='555.555.5555'
where employee_id = 100;

update departments set
department_name = 'Executive office'
where department_name = 'Executive';

insert into employees (employee_id, first_name, last_name, email, phone_number, hire_date,job_id,salary, commission_pct, manager_id, department_id)
values (employees_seq.nextval, 'Vasyl','Pupkin', 'Vasyl_Pupkin@epam.com', '123.456.7890',SYSDATE, 'IT_PROG', 10000, null, 100, 60); merge into emp_directory old
using (
select
e.employee_id
,e.first_name
,e.last_name
,e.email
,e.phone_number
,j.job_title
,d.department_name
,l.city
from employees e
inner join jobs j on e.job_id = j.job_id
left outer join departments d on e.department_id = d.department_id
left outer join locations l on d.location_id = l.location_id
) new
on (old.employee_id = new.employee_id)
when matched then update set
old.first_name = new.first_name
,old.last_name = new.last_name
,old.email = new.email
,old.phone_number = new.phone_number
,old.job_title = new.job_title
,old.department_name = new.department_name
,old.city = new.city
when not matched then insert (employee_id,first_name,last_name,email,phone_number,job_title, department_name,city)
values (new.employee_id,new.first_name,new.last_name,new.email,new.phone_number,
new.job_title, new.department_name,new.city); Other MERGE features:

WHERE clause for both insert and update parts;

DELETE WHERE clause in update part - to delete some of updated rows; -- Task: for all employees set hire_date
-- to the very first day in company.
-- The very first day - i.e. take job history into account. -- Just for this example create table with first days
create table first_days as
select employee_id, min(start_date) as first_day
from job_history
group by employee_id;

alter table first_days
add constraint first_days_PK
primary key (employee_id); -- Change hire_date to first_day. Memento NULLs!
UPDATE employees e
SET e.hire_date = (select fd.first_day
from first_days fd
where fd.employee_id = e.employee_id); -- Safer but more complex
UPDATE employees e
SET e.hire_date = (select fd.first_day
from first_days fd
where fd.employee_id = e.employee_id)
WHERE exists (select null
from first_days fd
where fd.employee_id = e.employee_id);
Full transcript