How to pass two tables to one and update the codes

Question

How to pass two tables to one and update the codes

Navigation

#1 by (0 votes)

1

Some time ago I created two tables, under different names, (for lack of experience), today I noticed that they had exactly the same fields. So I decided to put them together. But I need to do this without losing data.

What command can I make taking into account that new codes will be generated (since the key is auto increment ) and do I need to update the other tables with the new generated code?

There is something like this:

insert into x select * from y and z

And still updating with new codes?

Table Table

cd_quadro         int
nm_medida         varchar(30)
cd_progresso      int
impresso          bool
entregue          bool

Panel Table

cd_painel         int
nm_medida         varchar(30)
cd_progresso      int
impresso          bool
entregue          bool

Event Table

cd_evento         int
nm_evento         varchar(100)
cd_quadro         int
cd_painel         int

mysql

asked by anonymous 07.05.2014 / 16:29

1 answer

Error saving data Anaconda / osx - change the default version of python

score 0 · Accepted Answer

You can create a temporary field in the Table

ALTER TABLE Quadro ADD cd_painelInteger

Insert records

INSERT INTO Quadro (nm_medida, cd_progresso, impresso, entregue, cd_painel)
SELECT nm_medida, cd_progresso, impresso, entregue, cd_painel FROM Painel

Update Dependencies

UPDATE Evento E set cd_quadro = 
    (SELECT 
         cd_quadro 
     FROM 
         Quadro Q 
     WHERE 
         Q.cd_painel = E.cd_painel)
WHERE
    E.cd_Quadro is Null and E.cdEvento IS NOT NULL;

Drop the foreign keys and the table panel