mysql - 使用两个不同的 WHERE 条件更新 MySQL 中的两个表

Question

我目前对两个数据库有两个查询。第一个将给定 user_id 的所有值设置为 0，第二个将每个用户的排名更新为 1。

UPDATE table_1
JOIN table_2 ON table_1.user_id = table_2.user_id
SET table_1.value = 0,
table_2.total_value = 0
WHERE table_2.user_id = %s AND table_1.user_id =%s

和

UPDATE table_1
JOIN table_2 ON table_1.user_id = table_2.user_id
SET table_1.value = 555,
table_2.total_value =1555
WHERE table_1.rank =1

我想将它们整合在一起，这可能吗..？

Answer 1

UPDATE table_1
JOIN table_2 ON table_1.user_id = table_2.user_id
SET table_1.value = case when table_2.user_id = %s AND table_1.user_id =%s 
                         then 0
                         else table_1.value
                    end,
    table_1.value = case when table_1.rank =1 then 555 else table_1.value end,
    table_2.total_value = case when table_2.user_id = %s AND table_1.user_id =%s 
                               then 0
                               else table_2.total_value 
                          end,
    table_2.total_value = case when table_1.rank =1 then 1555 else table_2.total_value end
WHERE 
(
  table_2.user_id = %s AND table_1.user_id =%s
)
OR table_1.rank =1