CODEWITHSUNDEEP
mysql
wlraider70
wlraider70

Reputation: 186

change value in 1 table based on value in different table

I'm not totally clear how to ask this question, but I want to set all the poeple in a group to also be in a new group.

I want to do this... 

mysql> UPDATE  `cla-constituents`.`person_custom` SET  `c3` =  '6' WHERE `person_per`.`per_cls_id` =7;

but I get "ERROR 1054 (42S22): Unknown column 'person_per.per_cls_id' in 'where clause'"

cla-constituents is the DB, person_custom is table 1, c3 is the target cell

I want to change that cell for all people who have a 7 in the per_cls_id column on the person_per table. Same DB.

I hope that makes sense.

Upvotes: 0

Views: 40

Answers (1)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can use a LEFT or INNER JOIN

UPDATE  `cla-constituents`.`person_custom` 
INNER JOIN person_per ON cla-constituents.id = person_per.id
SET  `c3` =  '6' 
WHERE `person_per`.`per_cls_id` =7;

Here i have joined with an imaginary column. You can use your correct one.

Upvotes: 1

Related Questions