sql - 将现有表数据迁移到新表，并根据查询结果更改列值到其他表

Question

所以我有 2 个相同的表 table1 和 table2。我想将数据从 table1 迁移到 table2。

我可以使用这个查询:-

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

但在 definition_string 列中还有一些更改要做。

新表 table2 的列 definition_string 现在应该将电子邮件替换为 table1 中的用户名。

例如-

表 1

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

上面的definition_string应该替换为-

表2

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

将电子邮件替换为用户名应基于对表user的查找-

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

结果:

username=

usera
userx
usery

上述查询可能会产生一个或多个用户名。如果查询结果有多个用户名，那么我们只会考虑第一个进行替换。

虽然不正确但我试过了-

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;

我正在考虑编写一个循环遍历 table1 的所有行的 for 循环。但是循环好像效率不高？

Answer 1

我通常会回避过程循环，但这个问题并不适合纯 SQL 解决方案(至少我看不到)，所以我认为循环是可行的方法。下面是一个示例，说明如何将 table1 转换为 table2，并进行适当的转换:

create or replace function update_table() returns void as $$
declare
  rw table2%rowtype;
  prefix text;
  emails text;
  emaili text;
  user_id text;
begin
  for rw in select * from table1
  loop
    prefix := substring (rw.definition_string from '(SEND \w+ TO) ');
    emails := substring (rw.definition_string from 'SEND \w+ TO (.+)');

    foreach emaili in array string_to_array (emails, ' ')
    loop
      select username
      into user_id
      from users where email = emaili;

      prefix := prefix || ' ' || user_id;
    end loop;

    insert into table2 values
    (nextval ('table2_id_seq'), prefix);
  end loop;
end;
$$
language plpgsql;

或者，您可以使用相同的概念并在函数中划分翻译:

create or replace function email_to_user(email_address text) returns text as $$
declare
  prefix text;
  emails text;
  emaili text;
  user_id text;
begin
  prefix := substring (email_address from '(SEND \w+ TO) ');
  emails := substring (email_address from 'SEND \w+ TO (.+)');

  foreach emaili in array string_to_array (emails, ' ')
  loop
    select username
    into user_id
    from users where email = emaili;

    prefix := prefix || ' ' || user_id;
  end loop;

  return prefix;
end;
$$
  language plpgsql;

简单的概念证明:

select id, definition_string, email_to_user(definition_string) from table1

或者:

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

或者，如果您真的想更新现有记录，无论它们是如何到达那里的:

update table2
set definition_string = email_to_user (definition_string)
where definition_string != email_to_user (definition_string)