I have a clients table containing CLIENT_ID, CLIENT_NAME, and another table containing CLIENT_APROVED.
The approval table is populated with SOME clients of the clients table but only the NAMES of the clients. I wanted to replace the name with the client id. Or create a customer_id column in the table of approval. I wanted to do this in php. How can I mount this sql query?
The ideal would be to create a id_cliente column in the CLIENTE_APROVADO table 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
You should first add a column to the table aprovacao
ALTER TABLE aprovacao ADD id_cliente BIGINT;
Now you need to add id_cliente I suggest you use a UPDATE with SELECT , it would be +/- so.
UPDATE aprovacao
SET aprovacao.id_cliente = clientes.id_cliente
FROM clientes
WHERE aprovacao.NOMES = clientes.NOMES and aprovacao.id_cliente <> null
Create a foreign key for the aprovacao table:
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)