Return the id of a table in another table

Asked

Viewed 164 times

2

I have a client table containing ID_CLIENTE, NOME_CLIENTE and another approval table containing CLIENTE_APROVADO.

The approval table is filled with SOME clients in the client table but only the clients' NAMES. I wanted to replace the name with the client id. Or create an id_client column in the approval table. I wanted to do this in php. How can I mount this sql query ?

php mysql sql
  • 2

    Would you have any chance to add the Approved Customer field to the Customers table and delete the Approval table? I see no need to have another table only for approval. What is the idea of the existence of the Approval table?

  • Actually, you can include the approved cliente_field in the clients table and define it as a logical value.

  • The best idea would be to create a foreign key in the table aprovacao and making a comparison with the names repeated, and doing an update directly at the bank, as in Levi Macedo’s reply.

3 answers

1

The ideal would be to create a column id_cliente on the table of CLIENTE_APROVADO and migrate as follows:

update CA
set cA.id_cliente = CC.IDCLIENTE
from CLIENTE_APROVADO CA WITH(NOLOCK)
inner join CLIENTES CC ON CC.NOMECLIENTE = CA.NOMECLIENTE

0

First Voce should add a column in the table aprovacao

ALTER TABLE aprovacao ADD id_cliente BIGINT;

Now Voce needs to add the id_cliente I suggest you use a UPDATE with the SELECT, would be +/- like this.

UPDATE aprovacao SET aprovacao.id_cliente = clientes.id_cliente FROM clientes WHERE aprovacao.NOMES = clientes.NOMES and aprovacao.id_cliente <> null

0

Create a foreign key for the table aprovacao:

ALTER TABLE `aprovacao` ADD CONSTRAINT `fk_id_cliente` FOREIGN KEY ( `ID_CLIENTE` ) REFERENCES `clientes` ( `ID_CLIENTE` );

Then just update the data by comparing the name between the two tables: 

UPDATE AP
set AP.ID_CLIENTE = CL.ID_CLIENTE
FROM clientes CL
INNER JOIN aprovacao AP ON (CL.NOME_CLIENTE = AP.CLIENTE_APROVADO)

Browser other questions tagged php mysql sql

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