CODEWITHSUNDEEP
phpmysqllaravel
enchance
enchance

Reputation: 30411

MySQL: Update multiple rows based on 2 fields using only a single query

Is there a proper way to simplify this query from using 3 separate queries to only using 1? As you can see each INSERT is based on the fields option and 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));

Upvotes: 0

Views: 68

Answers (1)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81137

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));

Upvotes: 2

Related Questions