How to copy column data from one table to another table

Question

I have a table called UC which has an email column, and I have a backup table of it.

I need to copy the data (emails) from the table’s email column backup for the column email table uc, but I don’t know how to do it.

I made several attempts, without success. I had never come across this situation, copy data between tables.

update uc 
set uc.email =  backup.email
from uc , backup
where uc.idconsumidor =  backup.idconsumidor;

Answer 1

UPDATE uc u
   SET (email) = (SELECT b.email
                  FROM backup b
                  WHERE u.idconsumidor = b.idconsumidor)
WHERE EXISTS (
    SELECT 1
      FROM backup b2
     WHERE u.idconsumidor = b2.idconsumidor)

The outside WHERE (the 2nd) exists to prevent you from updating records in the table u that do not have correspondents in the table backup. Without this Where, records without matching would have the column email set to NULL.

Answer 2

The script to update the email from the backup table can be done as follows:

/* AMBIENTE DE TESTE: Tabelas temporárias para testar o script */
create global temporary table tmp_uc
(   
    idconsumidor int,
    email varchar2(100)
)
on commit preserve rows;

create global temporary table tmp_backup
(   
    idconsumidor int,
    email varchar2(100)
)
on commit preserve rows;

--Dados para teste
insert into tmp_uc (idconsumidor, email) values (1, 'AAAAA@uc');
insert into tmp_uc (idconsumidor, email) values (2, 'BBBBB@uc');
insert into tmp_uc (idconsumidor, email) values (3, 'CCCCC@uc');
insert into tmp_uc (idconsumidor, email) values (4, 'DDDDD@uc');
insert into tmp_uc (idconsumidor, email) values (5, 'EEEEE@uc');
insert into tmp_uc (idconsumidor, email) values (6, 'FFFFF@uc');
insert into tmp_uc (idconsumidor, email) values (7, 'GGGGG@uc');
insert into tmp_uc (idconsumidor, email) values (8, 'HHHHH@uc');
insert into tmp_uc (idconsumidor, email) values (9, 'IIIII@uc');

insert into tmp_backup (idconsumidor, email) values (1, 'AAAAA@bkp');
insert into tmp_backup (idconsumidor, email) values (2, 'BBBBB@bkp');
insert into tmp_backup (idconsumidor, email) values (3, 'CCCCC@bkp');
insert into tmp_backup (idconsumidor, email) values (4, 'DDDDD@bkp');
insert into tmp_backup (idconsumidor, email) values (5, 'EEEEE@bkp');
insert into tmp_backup (idconsumidor, email) values (6, 'FFFFF@bkp');
insert into tmp_backup (idconsumidor, email) values (7, 'GGGGG@bkp');

/* Atualização dos e-mails na tabela temporária tmp_uc para fins de teste */
update 
(   select
            u.email as email_U,
            bk.email as email_BK
    from tmp_uc u
    inner join tmp_backup bk on u.idconsumidor = bk.idconsumidor
    --where (...)
) t
set t.email_U = t.email_BK

select * from tmp_uc;

commit;

/*
idconsumidor   email
    1          AAAAA@bkp
    2          BBBBB@bkp
    3          CCCCC@bkp
    4          DDDDD@bkp
    5          EEEEE@bkp
    6          FFFFF@bkp
    7          GGGGG@bkp
    8          HHHHH@uc
    9          IIIII@uc
*/

If the script has worked as desired, you can use the following script for your physical tables:

-- Atualização dos dados na tabela física
    update 
    (   select
            u.email as email_U,
            bk.email as email_BK
        from UC u
        inner join backup bk on u.idconsumidor = bk.idconsumidor
        --where (...)
    ) t
    set t.email_U = t.email_BK

    commit;

Answer 3

I think we missed the COMMIT at the end:

update uc 
set uc.email = backup.email
from backup
where uc.idconsumidor =  backup.idconsumidor;

commit;

Answer 4

You can also do it with a for:

BEGIN
  FOR rec IN (SELECT bk.email
                    ,uc.id_consumidor
                FROM uc
                    ,backup bk
               WHERE uc.idconsumidor = bk.idconsumidor)
  LOOP
    UPDATE uc
       SET uc.email = rec.email
     WHERE uc.idconsumidor = rec.id_consumidor;
  END LOOP;
END;

In the query of the for I already made the Join between the tables, so I guarantee that it will only bring records that really need to be changed. Inside the loop I update.