Migrate data from one table column to another table column

Asked

Viewed 217 times

-1

I work in a company where we implement a new functionality in our system, but previously a field of our table was bug_passos varchar(500), now with the new implementation, we create a new table with the following fields bugp_id bigint(20), bugp_titulo varchar(500) e bugp_ordem int(4).

I wonder how I do to migrate the old data from the column bug_passos which is another table for this new table. Someone would give me a light?

Old Table:

`CREATE TABLE `tb_bug` (
  `bug_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bug_id_tenancy` bigint(20) DEFAULT NULL,
  `bug_data_criacao` datetime DEFAULT NULL,
  `bug_data_atualizacao` datetime DEFAULT NULL,
  `bug_descricao` varchar(1000) DEFAULT NULL,
  `bug_passos` varchar(500) DEFAULT NULL,
  `bug_status` varchar(40) DEFAULT NULL,
  `bug_fase` varchar(15) DEFAULT NULL,
  `bug_gravidade` varchar(8) DEFAULT NULL,
  `bug_titulo` varchar(160) DEFAULT NULL,
  `bug_informacoes_gerais` varchar(500) DEFAULT NULL,
  `bug_pro_id` bigint(20) DEFAULT NULL,
  `bug_usu_id_criador` bigint(20) DEFAULT NULL,
  `bug_emp_id` bigint(20) DEFAULT NULL,
  `bug_tipo` varchar(20) DEFAULT NULL,
  `bug_usu_id_executor` bigint(20) DEFAULT NULL,
  `bug_dem_id` bigint(20) DEFAULT NULL,
  `bug_prioridade` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`bug_id`),
  KEY `bug_pro_id` (`bug_pro_id`),
  KEY `bug_usu_id_criador` (`bug_usu_id_criador`),
  KEY `bug_emp_id` (`bug_emp_id`),
  KEY `bug_usu_id_executor` (`bug_usu_id_executor`),
  KEY `bug_dem_id` (`bug_dem_id`),
  CONSTRAINT `tb_bug_ibfk_1` FOREIGN KEY (`bug_pro_id`) REFERENCES `tb_projeto` (`pro_id`),
  CONSTRAINT `tb_bug_ibfk_2` FOREIGN KEY (`bug_usu_id_criador`) REFERENCES `tb_usuario` (`usu_id`),
  CONSTRAINT `tb_bug_ibfk_3` FOREIGN KEY (`bug_emp_id`) REFERENCES `tb_empresa` (`emp_id`),
  CONSTRAINT `tb_bug_ibfk_4` FOREIGN KEY (`bug_usu_id_executor`) REFERENCES `tb_usuario` (`usu_id`),
  CONSTRAINT `tb_bug_ibfk_5` FOREIGN KEY (`bug_dem_id`) REFERENCES `tb_demanda` (`dem_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2807 DEFAULT CHARSET=utf8`

Current Table

CREATE TABLE `tb_bug_passo` (
  `bugp_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bugp_titulo` varchar(500) DEFAULT NULL,
  `bugp_bug_id` bigint(20) NOT NULL,
  `bugp_ordem` int(4) DEFAULT NULL,
  PRIMARY KEY (`bugp_id`),
  KEY `fk_bug_passos` (`bugp_bug_id`),
  CONSTRAINT `fk_bug_passos` FOREIGN KEY (`bugp_bug_id`) REFERENCES `tb_bug` (`bug_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18767 DEFAULT CHARSET=utf8
java mysql database spring mysql-workbench

  • It seems that the data in the new table will be separated following some criteria. You can mount a precedent and with some cursors selects the data from one table and inserts into the other following the pre-set criteria.

  • Could you give me an example of the trial that would work for this case, I already did a search and I couldn’t find anything half like it :/ I’m a little desperate with this task already kkkk

  • It’s not just a select into?

  • Welcome to Sopt! First you need to let us know if you want to do an update or an update. It’s very different, when you want to register data from one table to another table, or if you want to update only a new empty field that has pre-existing data in the new table.

  • @Ivanferrer I intend to update the column data of a new table, however, the data I intend to insert already exists in the column of another table. When I did the insert into returned that the bugp_bug_id which is my link key between the two tables does not have a default value. INSERT INTO
 tb_bug_passo(bugp_titulo) SELECT bug_passos
FROM
 tb_bug


  • So in this case, if you already have the other data, you no longer need Insert, it’s update. Try using a subquery to update.

  • From what I understand, you are making an auxiliary table, correct... which relates n ... n, and you need to import the relational data into this table, this is it?

  • @Ivanferrer Exactly that...

  • You cannot use auto_increment in the ID field that will import the auto_increment keys to this auxiliary table... it must be a numeric field... vc can even make an update relation between the two, like, if an update, the helper updates tb..., only do not recommend to delete.

  • Right, I’ll try to do the Update here. If it works out you saved me hehe vlw by patience, I’m pretty inexperienced with comics.

  • Then, leave this table empty, it may even have an auto_increment, but it should not be the same ID of the old table, it becomes a foreign key (foreign_key).

Show 6 more comments

2 answers

3


insert into TB_NEW (field_new) select field_old from TB_OLD;

remembering that the same amount of columns in the Insert has to be equal in the select, and the order also, if you need to refine the data further, use the WHERE.

0

I was able to solve with the following SQL:

   INSERT INTO
    tb_bug_passo (bugp_bug_id, bugp_titulo) 
SELECT 
    bug_id, bug_passos
FROM 
    tb_bug

Tbm made a Stored Procedure that entered the order for me separately.

  • 1

    The reply from @godoyAlexandre is exactly what you did, the difference is that it made a minimalist example.

Browser other questions tagged java mysql database spring mysql-workbench

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