mysql query inner join condition where id IN

Question

Can you suggest the best way to have simple query where I have 2 tables where I need to update table 1 status column and then if another column is 1 then update another table2. The main question is how to do it when I am using where id IN ids

UPDATE table1 SET status = 1 WHERE id IN (ids)

and if columnX for each Id is 1 then

UPDATE table2 SET status = 1 for that id

Answer 1

Not entirely sure what you mean, but I'm guessing you can use some variant of UPDATE JOIN, basic syntax is like:

UPDATE table1 t1
  JOIN table2 t2
    ON t2.table1_id = t1.id
   SET t1.status = 1,
       t2.status = CASE 
         WHEN t1.columnX = 1 THEN 1 /* Update if columnX is 1 */
         ELSE t2.status             /* Use old status if not  */
       END CASE
 WHERE t1.id IN (...)

If there is a low prevalence of columnX being 1 then you could split out the second part and do a separate query in a transaction:

UPDATE table1 t1
   SET t1.status = 1
 WHERE t1.id IN (...)   

UPDATE table2 t2
  JOIN table1 t1
    ON t1.id = t2.table1_id
   AND t1.columnX = 1
   SET t2.status = 1
 WHERE t2.table1_id IN (...)