SQL copy and replace records

user_8248683 0
Viewed 93

I am quite new to SQL and having troubles with my code. I have been trying to copy a line in a table and replacing part of the record. However, I keep getting the error:

ORA-00907: missing right parenthesis

The following code gives the error:

  insert into mi_structure
  select replace parent_mi_id, (child_mi_id,'GR','GR_V') child_mi_id, startdate, enddate, mutnr
  from mi_structure
  where parent_mi_id like 'MIPFV%29'
    and sysdate between startdate and enddate;

The table mi_structure with the following code looks like this

  select *
  from mi_structure
  where parent_mi_id like 'MIPFV%29'
    and sysdate between startdate and enddate;


PARENT_MI_ID   ||   CHILD_MI_ID       ||   STARTDATE   ||   ENDDATE   || MUTNR
MIPFV_POOL 29  ||   CSLLXXXX.USD.GR   ||   42917       ||   36526     ||   11

What am I doing wrong?

oracle-sqldeveloper oracle sql
2 Answers

select replace parent_mi_id, (child_mi_id,'GR','GR_V') child_mi_id, startdate, enddate, mutnr from mi_structure

it seems you are using replace function wrong it should be like the below 

insert into mi_structure (parent_mi_id_col,child_mi_id_col,startdate_col,enddate,mutnr) 
  select parent_mi_id, replace(child_mi_id,'GR','GR_V') child_mi_id, startdate, enddate, mutnr
  from mi_structure
      where parent_mi_id like 'MIPFV%29'
      and sysdate between startdate and enddate;

btw I recommand you add the columns in the insert as above.

user_22918690

could you try this

  insert into mi_structure
  select parent_mi_id, replace (child_mi_id,'GR','GR_V') child_mi_id, startdate, enddate, mutnr from mi_structure
  where parent_mi_id like 'MIPFV%29'
  and sysdate between startdate and enddate;
user_85001100
Related