Update table using select and where

I have two tables:

table ficha_atendimento
int id
string cidadaos_cns

table cidadaos
int id
string cns

I need do create a column int cidadaos_id on table ficha_atendimento_cidadao and I like to fill it with the id from cidadaos table, like it:

insert into ficha_atendimento
set cidadao_id = select id from cidadaos where cidadaos.cns = ficha_atendimento.cns

But it’s not works… Any help?

Answer

You need to do two separate operations to achieve that. Firstly, add the cidadao_id column to your table. Then, UPDATE its values from the cidados table:

ALTER TABLE ficha_atendimento ADD COLUMN cidadao_id INT NOT NULL;
UPDATE ficha_atendimento f
JOIN cidadaos c ON c.cns = f.cns
SET f.cidadao_id = c.id

You may also want to add a FOREIGN KEY on the cidadao_id value:

ALTER TABLE ficha_atendimento ADD FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id)