How to pass two tables to one and update codes

Asked

Viewed 69 times

1

Long 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 unite them. However, I need to do this without losing the data.

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

Is there anything like:

insert into x select * from y and z

And still updating with the new codes?

Table

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

Table Panel

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

Table Event

cd_evento         int
nm_evento         varchar(100)
cd_quadro         int
cd_painel         int
mysql

  • The event can have a panel and a frame or only one of the 2?

  • You can have both, only one or none; but now that you’ve asked... you really think it’s necessary to have two equal tables with different names?

  • 1

    No, just a table with a type field, for example 1 for frame and 2 for panel.

  • @Lizard, you’ve solved the problem?

1 answer

0


You can create a temporal field in the Frame table

ALTER TABLE Quadro ADD cd_painelInteger

Enter the records

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

Update the 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

Browser other questions tagged mysql

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