Duplicate column of a table for itself by changing only the ID

Asked

Viewed 33 times

0

Good morning everyone, I have a doubt and I found nothing similar that can help me, I need to create a copy and put function or duplicate everything this information and generates a new ID keeping the information.

I’m doing a project listing and control system for the company I work for, following a table where I need the fields to be duplicated.

CREATE TABLE `tb_projetos` (
      `id` int(11) NOT NULL,
      `empresa` varchar(128) NOT NULL,
      `nome` varchar(128) NOT NULL,
      `tipo` varchar(128) NOT NULL,
      `localidade` varchar(128) NOT NULL,
      `aceitacao_projeto` varchar(128) NOT NULL,
      `prazo_projeto` varchar(128) NOT NULL,
      `mub_colaborador` varchar(128) NOT NULL,
      `mub_inicio` varchar(128) NOT NULL,
      `mub_fim` varchar(128) NOT NULL,
      `lev_colaborador` varchar(128) NOT NULL,
      `lev_inicio` varchar(128) NOT NULL,
      `lev_fim` varchar(128) NOT NULL,
      `dig_colaborador` varchar(128) NOT NULL,
      `dig_inicio` varchar(128) NOT NULL,
      `dig_fim` varchar(128) NOT NULL,
      `cal_colaborador` varchar(128) NOT NULL,
      `cal_inicio` varchar(128) NOT NULL,
      `cal_fim` varchar(128) NOT NULL,
      `art_trt` varchar(128) NOT NULL,
      `engenheiro` varchar(128) NOT NULL,
      `doc_colaborador` varchar(128) NOT NULL,
      `doc_data` varchar(128) NOT NULL,
      `doc_fim` varchar(128) NOT NULL,
      `rev_colaborador` varchar(128) NOT NULL,
      `rev_data` varchar(128) NOT NULL,
      `doc_envio_colaborador` varchar(128) NOT NULL,
      `doc_envio_envio` varchar(128) NOT NULL,
      `doc_envio_entrega` varchar(128) NOT NULL,
      `doc_envio_aprovacao` varchar(128) NOT NULL,
      `protocolo_ei` varchar(128) NOT NULL,
      `situacao` varchar(128) NOT NULL,
      `concessionaria` varchar(128) NOT NULL,
      `observacao` varchar(256) NOT NULL,
      `adicionado` date NOT NULL,
      `modificado` date DEFAULT NULL
    )

making some tests, I arrived at this formula that when testing in the bd works but in the interpretation of the code it does not execute correctly.

include('../../class/db_conexao.php');  $id = 0;
$conexao->query("CREATE TEMPORARY TABLE tmp SELECT * FROM tb_projetos WHERE id=$id;") or die($conexao->error); 
$conexao->query("UPDATE tmp SET id=$id+1 WHERE id = $id;") or die($conexao->error); 
$conexao->query("INSERT INTO tb_projetos SELECT * FROM tmp WHERE id=$id+1;") or die($conexao->error);
php mysql sql

  • suppose the table has only "id, company, name", would make a insert into tabela (id, empresa, nome) select 1 as novoid, empresa, nome from tabela simple, just for all fields and Where you need and of course change the value of "novoid", for example "id + 1000 as novoid"

  • hi, all good. I made a test here with an example on the internet and put it directly by mysql and it worked but tried to interpret in the code and it does not generate the copy. include('../../class/db_conexao.php');

$id = 0;
$conexao->query("CREATE TEMPORARY TABLE tmp SELECT * FROM tb_projetos WHERE id=$id;") or die($conexao->error);
$conexao->query("UPDATE tmp SET id=$id+1 WHERE id = $id;") or die($conexao->error);
$conexao->query("INSERT INTO tb_projetos SELECT * FROM tmp WHERE id=$id+1;") or die($conexao->error);

1 answer

1

I will use a simplified version of the original table to exemplify the solution. The solution for the final table is the same, adding the remaining fields.

The advantage of this approach is that there is no need for an intermediate table. Even the solution of using an intermediate table can be done using a temporary table - through the syntax WITH - that has the advantage of not instantiating "physically" in DB.

Let’s go to the solution that seems the simplest:

CREATE TABLE `tb_projectos` (
  `id` int(11) NOT NULL,
  `empresa` varchar(128) NOT NULL,
  `nome` varchar(128) NOT NULL,
  `tipo` varchar(128) NOT NULL
);

The desired result is obtained as follows::

INSERT INTO tb_projectos 
SELECT
  max_id.id + src.id,
  src.empresa,
  src.nome,
  src.tipo
FROM tb_projectos src
  JOIN (SELECT MAX(ID) + ABS(MIN(ID)) + 1 AS ID FROM tb_projectos) max_id ON 1 = 1;

The new Dice is calculated by adding the highest value of the field id, with the smallest module in the table at the date of insertion and the value initially assigned to the field id. To this value 1 is added to ensure that there is no collision even if the smallest Indice is 0.

A question: Why not use the id column as auto-incremented key? This way, the concern of the single index is delivered to DB and the replication Index becomes even simpler:

INSERT INTO tb_projectos (empresa, nome, tipo)
SELECT empresa, nome, tipo
FROM tb_projectos;

The table would have to be defined by assigning the property AUTO_INCREMENT to the countryside id and promote it the primary key.

CREATE TABLE `tb_projectos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empresa` varchar(128) NOT NULL,
  `nome` varchar(128) NOT NULL,
  `tipo` varchar(128) NOT NULL,
  PRIMARY KEY (id)
);

Browser other questions tagged php mysql sql

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