CODEWITHSUNDEEP
mysqlsqlselect
Italo Rodrigo
Italo Rodrigo

Reputation: 1785

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?

Upvotes: 0

Views: 40

Answers (3)

Ramadhani
Ramadhani

Reputation: 7

The principles are:

INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;

if you put according to the rules i think you can run your code. And plus if you have some data to demos it would be help.

Upvotes: 1

Nick
Nick

Reputation: 147266

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)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You seem to want an update:

update ficha_atendimento fa join
       cidadaos c
       on c.cns = fa.cns
    set fa.cidadao_id = c.id ;

Upvotes: 1

Related Questions