Oracle Conditional Insert Statement
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;
something like:
insert into tableA(a,b,c) select x, case x when 'Sigma' then y when 'Teta' then 'Invalid', x from tableB;
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
END,
z
FROM tableb;
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
END,
z
FROM tableb;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial