How to mass insert or update in a single query (not a for loop of queries) using Laravel 4.2

Question

$cardQueryList = [];
foreach($cards as $cardName => $quantity) {
    $cardQueryList[] = [
        'username' => $user->username,
        'card_uid' => $card->uid,
        'have_quantity' => $quantity
    ];
}

Collection::insert($cardQueryList);

The above code creates new rows even if the row exists. How can I make it so if the row exists, it updates. And if it doesn't it creates the row? An Eloquent or Fluent answer would be optimal but I'm open to raw if there's no other way.

I would like to do a mass update/insert with a single query. Not a for loop of queries for every record. Ideally I'd like to hit the database once for obvious reasons.

Also I've already checked the following link:

Insert a new record if not exist and update if exist, laravel eloquent

The above works for a single record update/insert. Which if I ran with a for loop would be very slow. I'm looking for an answer that allows a mass insert/update in a single query.

Note: I'm using, both 'username' and 'card_uid' as my key. So basically when I find a row with said username and card_uid, I'd like to update the corresponding row. Otherwise create a new row.

Answer 1

Typically the sort of sql you would be using would be something along the lines of the following:-

    insert into `TABLE` ( `FIELD1`,`FIELD2`, `FIELD3` ) values ( 'VALUE1','VALUE2','VALUE3' )
    on duplicate key
        update
            `FIELD1`='VALUE1',
            `FIELD2`='VALUE2',
            `FIELD1`='VALUE3';

How you would use this with laravel I couldn't tell you! Oops - forgot the field names in the update part

Answer 2

<?php

$valuesArray = [];

foreach( $cards as $cardName => $quantity ) 
{
    $valuesArray[] = "('".$user->username."','".$card->uid."','".$quantity."')";   
}

$db->query 
(
    "INSERT INTO `TABLE` ( `username`,`card_uid`, `have_quantity` ) VALUES ".implode( ',', $valuesArray )."
    ON DUPLICATE KEY UPDATE `have_quantity` = VALUES(`have_quantity`);"
 );

Make sure you have a primary key on username and card_uid. Also don't forget to escape the values and to only run the query if $valuesArray is not empty.