Update mysql table only if all entries of another table match a certain condition-CodePudding

is it possible in MySQL to update a certain table only if all of the rows of another table match a certain condition? Let me give you an example with a database having two tables:

TableA: id INT, completed BOOLEAN

TableB: total INT, progress INT, tableA_id INT

Can I update TableA setting 'completed=1' if all of the entries of TableB have total==progress? I was thinking about using CASE:

UPDATE TableA SET completed = CASE WHEN (SELECT..) THEN 1 ELSE 0 END WHERE id = x

Of course I don't know how to proceed... Thank you in advance for any help!

CodePudding user response：

Use a correlated subquery in the WHERE clause of the UPDATE statement which checks the min value of the boolean expression total = progress.
If it is 1 which means true, then there is no row in TableB where total <> progress:

UPDATE TableA a 
SET a.completed = 1
WHERE (SELECT MIN(b.total = b.progress) FROM TableB b WHERE b.tableA_id = a.id) = 1; -- For MySql you may even omit =1

If there is always at least 1 row in TableB for each TableA.id you could also use NOT EXISTS:

UPDATE TableA a 
SET a.completed = 1
WHERE NOT EXISTS (
  SELECT *
  FROM TableB b
  WHERE b.tableA_id = a.id AND b.total <> b.progress
);