php - MySQL:仅使用单个查询基于 2 个字段更新多行

Question

是否有适当的方法可以将此查询从使用 3 个单独的查询简化为仅使用 1 个？正如您所看到的，每个 INSERT 都基于字段 option 和 user_id。

DB::update('
    UPDATE bd_useroptions SET `value` = ?
        WHERE `option` = ? AND user_id = ?',
    array($page_pagination, 'page_pagination', $user->id));

DB::update('
    UPDATE bd_useroptions SET `value` = ?
        WHERE `option` = ? AND user_id = ?',
    array($newsletter, 'newsletter', $user->id));

DB::update('
    UPDATE bd_useroptions SET `value` = ?
        WHERE `option` = ? AND user_id = ?',
    array($shortcuts, 'shortcuts', $user->id));

Answer 1

DB::update("UPDATE bd_useroptions SET value = CASE
    WHEN option = 'page_pagination' THEN ?
    WHEN option = 'newsletter' THEN ?
    WHEN option = 'shortcuts' THEN ?
    END
WHERE user_id = ?",
array($page_pagination, $newsletter, $shortcuts, $user->id));