CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Phate
Phate

Reputation: 6612

select->insert->delete into one sql statement: possible?

I have 3 tables, T1 T2 and T3. Each table has the same columns, except for T3 which has one additional "code" column.

My logic is the following:

-I have to search for any rows in T1 which are also contained in T2.

-For each found row I have to move it in T3, this would mean deleting it from T1 and create it into T3, with code 100.

I know that oracle allows for an insert...from select statement, in which case I have this:

insert into T3 (100,c1,c2,c3)
select c1,c2,c3 from T1 where exists (select null from T2 where
 c1=T1.c1 and c2=T1.c2 and c3=T1.c3);

This solves the select/insert problem, but would it be possible to add a delete from T1 without having to repeat the select statement?

Upvotes: 0

Views: 672

Answers (2)

Ravi
Ravi

Reputation: 31417

You are probably looking for MERGE

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

Example

This example has all 3 DML operation (INSERT,UPDATE and DELETE)

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

Answer to OP

MERGE INTO tab3 D
   USING (SELECT col1 FROM tab1 where col1 in(select col1 from tab2)) S
   ON (D.col1 = S.col1)    
   WHEN NOT MATCHED THEN 
   INSERT (D.col1,D.code) 
   values(S.COL1,100);
    DELETE tab1 WHERE(col1 in(select col1 from tab2) );

I have tested and working fine.

Upvotes: 2

Devart
Devart

Reputation: 121952

Perhaps useful to use cycle:

BEGIN
FOR rec IN
(SELECT c1, c2, c3 from T1 WHERE EXISTS (SELECT NULL FROM T2 WHERE
  c1 = T1.c1 AND c2 = T1.c2 AND c3 = T1.c3))
  LOOP
INSERT INTO T3 (100, rec.c1, rec.c2, rec.c3);
DELETE FROM T1 WHERE T1.c1 = rec.c1
   and T1.c2 = rec.c2
   AND T1.c3 = rec.c3;
   END LOOP;
END;

Upvotes: 0

Related Questions