sql - 用一个范围内的随机数更新每条记录

Question

我想用从generate_series(9353500001234, 9353500001237) 并且还需要确保不应更新重复值。当我尝试将 employee 表与 generate_series 连接时，它会产生叉积。请指教

CREATE TABLE employee
(
  member_number integer,
  gs1_src_acct_num bigint,
  CONSTRAINT employee_gs1_src_acct_num_key UNIQUE (gs1_src_acct_num)
)  
INSERT INTO lcdm_lnd.employee(member_number) VALUES (7360909);
INSERT INTO lcdm_lnd.employee(member_number) VALUES (3142554);
INSERT INTO lcdm_lnd.employee(member_number) VALUES (1510423);
INSERT INTO lcdm_lnd.employee(member_number) VALUES (9605961);

Answer 1

我认为这可以满足您的需求:

update employee
    set gs1_src_acct_num = e2.new_gs1_src_acct_num
    from (select e2.*,
                 (row_number() over (order by random()) - 1 + 9353500001234) as new_gs1_src_acct_num
          from employee e2
         ) e2
     where e2.member_number = e.member_number;