So I have 2 identical tables table1 and table2. I want to migrate data from table1 to table2. 

I can use this query:-

```
insert into table2(table2_id, definition_string, description, active) 
select nextval('table2_id_seq'), definition_string, description, active from table1;

```

But there is some more change to do in column `definition_string`.

The column `definition_string` of new table `table2` should now have emails replaced with usernames which were in `table1`.

**For example-**

**table1**

> 
> definition\_string =
> 
> 
> 

```
SEND open TO abc@gmail.com
SEND close TO abc@gmail.com
SEND open TO def@gmail.com ghi@gmail.com
SEND close TO def@gmail.com ghi@gmail.com
SEND open TO jkl@gmail.com
SEND close TO jkl@gmail.com

```

The above definition\_string should be replaced to-

**table2**

> 
> definition\_string =
> 
> 
> 

```
SEND open TO usera
SEND close TO usera
SEND open TO userb userc
SEND close TO userb userc
SEND open TO userd
SEND close TO userd

```

The replacement of email to username should be based on lookup at table `user`-

```
select username from user where email = 'abc@gmail.com';

```

**RESULT:** 

> 
> username=
> 
> 
> 

```
usera
userx
usery

```

The above query may result in one or more usernames. If the query results in more than one usernames then we will only consider the first one for the replacement.

Although not correct but I tried this-

```
update 
  table2 
set 
  definition_string = case when definition_string = 'SEND open TO abc@gmail.com' then (
    select 
      username 
    from 
      user 
    where 
      email = 'abc@gmail.com'
  ) when definition_string = 'SEND close TO def@gmail.com ghi@gmail.com' then (
    select 
      username 
    from 
      user 
    where 
      email = 'def@gmail.com' || 'ghi@gmail.com'
  ) else definition_string -- don't change anything
  end;

```

I was thinking to write a for loop which iterates through all of the rows of `table1`. But seems like it is not efficient to loop over?