Oracle Conditional Insert Statement

GurcanK used Ask the Experts™
Dear Experts,

I would like to insert data from one table to another with some conditions.

Think that we have table-A(a,b,c) and table-B(x,y,z). I will do  "insert into Table-A select x,y,z from Table_B" but with the conditions: if x='Sigma' the y will be x (the inserted value will be same as field x), if x='Teta' the y will be character value 'Beta' while inserting.  

Best Regards
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
A case statement should work.

something like:
insert into tableA(a,b,c) select x, case x when 'Sigma' then y when 'Teta' then 'Invalid', x from tableB;
Most Valuable Expert 2011
Top Expert 2012
extending on what slightwv posted with some small corrections

INSERT INTO tablea(a, b, c)
     SELECT x, CASE x WHEN 'Sigma' THEN x WHEN 'Teta' THEN 'Beta' END, z
       FROM tableb;

You didn't specify what should happen if x is NOT either Sigma nor Teta.
The query above would return NULL for those values.
If the CASE should default to "y" in that scenario then try this instead

INSERT INTO tablea(a, b, c)
    SELECT x,
           CASE x WHEN 'Sigma' THEN x
                  WHEN 'Teta' THEN 'Beta'
                  ELSE y
      FROM tableb;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial