Copy records from one table to another via select

Asked

Viewed 2,413 times

1

I am copying the records from one table to another in different databases, but a column in the new table cannot be null and the data coming from the old table are null.

Is there a way to put some conditional to "turn" the null fields into something I want ?

Below the query I’m using

insert into banco_novo.cliente (codcliente, nome, cpf, rg, sexo, data_nascimento, endereco, bairro, cidade, estado, cep) (select codcliente, nome, cpf, rg, sexo, data_nascimento, endereco, bairro, cidade, estado, cep from banco_antigo.cliente)
mysql sql
  • 1

    You can use a Insert Select and in the select set the condition for fields that will be null.

1 answer

4


There is the function IF() with it you can "transform" the null values into some other value in your insert... select, see this example:

insert into table2(nome)
select if (nome is null,'padrao para nulo', nome)
from table1;

The values they saw as null of the field nome now contain the value 'padrao para nulo'.

See the code working on SQL Fiddle.

  • I solved it in a similar way: insert into banco_novo.cliente (codcliente, nome, cpf, rg, sexo, data_nascimento, endereco, bairro, cidade, estado, cep) (select codcliente, nome, cpf, rg, IFNULL(sexo, 'I'), IFNULL(data_nascimento, '2000-01-01'), endereco, bairro, cidade, IFNULL(estado, '0'), cep from banco_antigo.cliente)

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.