CODEWITHSUNDEEP
mysqlsql
Abdallah Abdillah
Abdallah Abdillah

Reputation: 1189

SQL UPDATE SET a column to be equal to a certain value in a related table that referenced by a different column?

There is a table of customers and table of call_card. card_id is the foreign key in customers table that reference call_card table

customers table

The image above is the table of customers

enter image description here

The image above is call_card table

what i want is
(if location_id and visit_id in customers table is equal to location_id and visit_id in call_card then i should set card_id in customers table to be equal with card_id in call_card).

What is the best approach to this scenario,
I apologize for not being word-perfect in English.

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

It sounds like a join and update:

update customers c join
       call_card cc
       on c.location_id = cc.location_id and c.visit_id = cc.visit_id
    set c.card_id = cc.card_id;

Upvotes: 1

Related Questions