wutzebaer wutzebaer - 1 year ago 45
SQL Question

How to spread a table into another one

We have a legacy table

create table table1 (col1 int);
insert into table1 values(1);
insert into table1 values(2);
insert into table1 values(3);

SELECT * FROM table1;

now it gets a new column

alter table table1 add column col2 int;
alter table table1 ADD CONSTRAINT unique1 UNIQUE (col2);

SELECT * FROM table1;

then we have another table

create table table2 (col1 int);
insert into table2 values(7);
insert into table2 values(8);
insert into table2 values(9);

SELECT * FROM table2;

now we want to spread the values of table 2 into table1.col2

UPDATE table1 up
SET col2 = (SELECT col1
FROM table2 t2
WHERE NOT EXISTS (SELECT 1 FROM table1 t1 WHERE t1.col2=t2.col1)

but the update statement does not see the already updated rows

ERROR: duplicate key value violates unique constraint "unique1"

Any ideas how to do that? It would be ok, if table1 remains with some rows col2=null if table2 has less rows than table1

Answer Source

This seems much easier with a join:

with t2 as (
      select t2.*, row_number() over (order by col1) as seqnum
      from table2 t2
update table1 t1
    set col2 = t2.col1
    from t2
    where t1.col1 = t2.seqnum;

If col1 in table1 is not strictly sequential, you can still do this:

with t2 as (
      select t2.*, row_number() over (order by col1) as seqnum
      from table2 t2
     t1 as (
      select t1.*, row_number() over (order by col1) as seqnum
      from table1 t1
update table1 toupdate
    set col2 = t2.col1
    from t1 join
         on t1.seqnum = t2.seqnum
    where toupdate.col1 = t1.col1;