Select from two tables and insert into two other at the same time

Asked

Viewed 611 times

0

I have four tables

tabela A  aic002
campos :
codigo data nome especie valor rg cpf exped

Tabela B excli
campos
codigo nome rg cpf exped


tabela C imovel
Campos
codigo imovel rua bairro cidade estado 

tabela D  escritura 
codigo  data especie valor  rua bairro cidade estado

I’d like to run a single insert that:

  • take the fields (date specify value) A and popularize in D that would be the scripture control table
  • take fields (name rg Cpf exped ) of A and popularia in B thus creating a new client table
  • take (street city state) of C and popularia in D to complete the addresses of customers

How to do this in a single insert knowing that:

Tables A, B and C are related by code but the only one that does not repeat the code is Immovel Table C.

mysql sql

  • If I understood what you want is not possible with just one insert, it takes at least two since two tables will be written. It is unclear how the relationship is being done. You say what you take, but what you take? You take the specified columns. How to know what the line is? BTW your name is Mark or Joelias?

  • This is Joelias Marcos. is that I wanted to do the Insert without having to update one of the already populated tables that in case it would be table d writing.So from what I understand I will be obliged to do the update...

  • It’s not right but I think it’s the way...

  • When you go to add information, edit the question and leave everything organized. I gave an improved because the question was very messy. In the commentary it becomes more difficult to read something and understand.

  • Insert into scriptures (Tipoimoveldescr,STREET,NEIGHBORHOOD,UF) select DISTINCT a. DESCRICAO,a. STREET,a. NEIGHBORHOOD,a. CITY,a. state from immovable to INNER JOIN scriptures b on a.BOOK = 'E0285155' INNER JOIN aic002 c on a.BOOK = c.CODIGO group by a.BOOK (rodo o select mas não devolve nada no select no select me no select)

1 answer

1

Joelias,

As commented, it is not possible to accomplish what you want with just one INSERT because we have two destinations to be inserted (B, D) but I believe that its objective is to avoid the use of UPDATE in table D. Try the following:

    INSERT INTO D(data, especie, valor, rua, bairro, cidade, estado)        
    SELECT A.data, A.especie, A.valor        
         , C.rua, C.bairro, C.cidade, C.estado        
      FROM A        
      JOIN C ON C.codigo = A.codigo        

    INSERT INTO B(nome, rg, cpf, exped)        
    SELECT A.nome, A.rg, A.cpf, A.exped FROM A

Note that I did not insert in the Inserts the column referring to the codes because it was not clear if the code is PRIMARY KEY (PK) or FOREIGN KEY (FK). From what I understand it is a FK. If it is, you should insert them into the insert as we did with the other columns.

  • Thank you very much Vinicius, I will test and return you if ok...

Browser other questions tagged mysql sql

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