Nikhil Vaghla Nikhil Vaghla - 2 months ago 8
MySQL Question

Update table with join in mysql

i want to update field in my database.
I have two table and table field like bellow

Table

operations_per_assembly
Field
operation_id,is_mecahnical


Table
operations
Field
id,repair_type_id


Now i want to update
is_mechanical
field where
repair_type_id
= 3

My query

UPDATE
`operations_per_assembly`
JOIN `operations`
ON `operations`.`id` = `operations_per_assembly`.`operation_id`
SET `operations_per_assembly`.`is_mechanical` = '4'
WHERE `operations_per_assembly`.`operation_id` = `operations`.`id`
AND `operations_per_assembly`.repair_type_id = 3


Please help me.

Answer

Put the repair_type_id = 3 condition in the join conditions. This way you are telling to join only on repair_type_id = 3 so you will only get those records.

  UPDATE 
  `operations_per_assembly` 
   JOIN `operations` 
     ON `operations`.`id` = `operations_per_assembly`.`operation_id` AND `operations`.repair_type_id = 3
   SET `operations_per_assembly`.`is_mechanical` = '4' 
Comments