CODEWITHSUNDEEP
sqloracle-databaseplsql
osfar
osfar

Reputation: 413

use if else in insert statement plsql

i have plsql code that insert data from table1 to table2

and i want to put if statment to insert certain value in a specific column and this is my code 

for rec_table1 in (
select * from table1 
where salary > 100)
loop
insert into table2 (
col1 , col2 , col3 col4 ,col5)
values 
(rec_table1.col1 , 
 rec_table1.col2,
 rec_table1.col3 , 
 rec_table1.col4 ,
if (rec_table1.col2 = 1)
  then
rec_table1.col2 
else
rec_table1.col5
end if

but it give me syntax error at the if statment

Upvotes: 0

Views: 4923

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can manage it within one statement by using decode() as : 

insert into table2 (col1 , col2 , col3 col4 ,col5)
select col1 , decode(col2,1,col2,col5) , col3, col4 ,col5 
  from table1 
 where salary > 100;

OR by using case..when expression as :

insert into table2 (col1 , col2 , col3 col4 ,col5)
select col1 , case when col2=1 then col2 else col5 end , col3, col4 ,col5 
  from table1 
 where salary > 100;

P.S. decode() is spesific to Oracle DB, case..when is generic.

Upvotes: 1

user330315
user330315

Reputation:

IF is a PL/SQL statement and can't be used inside a SQL statement.

You can use a SQL CASE expression: 

for rec_table1 in (select * from table1 
                   where salary > 100)
loop
  insert into table2 (col1, col2, col3, col4, col5)
  values (rec_table1.col1, rec_table1.col2,  rec_table1.col3, rec_table1.col4,
          case 
            when rec_table1.col2 = 1 then rec_table1.col2 
            else rec_table1.col5
          end);
END LOOP;

However the whole loop is inefficient and slow. You don't need it. This can be written as a single INSERT statement which will perform a lot better.

You can replace the complete LOOP .. END LOOP; part with the following INSERT statement:

insert into table2 (col1, col2, col3, col4, col5)
select col1, col2, col3, col4,           
       case 
          when col2 = 1 then col2 
          else col5
       end
from table1 
where salary > 100;

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

A plain insert into with case would do just fine. There's no need to go for loops. 

  INSERT INTO table2 (
     col1,col2,col3,col4,col5) 
        select col1 , col2,col3 col4,CASE col2
     WHEN 1 THEN col2
          ELSE col5
     END AS col5
     FROM table1 where salary > 100;

Upvotes: 1

Chiranjib
Chiranjib

Reputation: 1783

I believe, instead of If-Else, but you would have to implement Case-When syntax.

Here's the documentation: Case-When

First focus on constructing the correct Select statement, then you can just insert values using output of the select query.

Upvotes: 0

Related Questions