Insert into <table> on duplicate key update select from in query builder?

How (if possible) can I write the following query using Laravel's query builder?

INSERT INTO Summary (dy, foo, ct, blah_total)
        ON DUPLICATE KEY UPDATE
            ct = ct + VALUE(ct),
            blah_total = blah_total + VALUE(bt)
        SELECT  DATE(dt) as dy, foo,
                COUNT(*) as ct, SUM(blah) as bt)
            FROM Staging
            GROUP BY 1, 2;

So insert values from one table into another table, but aggregated, and if values exist update them. I've tried DB::table('...')->insertUsing() but that does not seem to do it and now I'm kinda unsure if it's even possible to do in query builder? Or do I have to go the hard route of writing this RAW and take care of sql injection etc myself?

jlrdw

5 years ago

Level 75

Have you looked at eloquents updateOrCreate.

https://laravel-news.com/firstornew-firstorcreate-firstor-updateorcreate

And query builder has https://laravel.com/docs/8.x/queries#update-or-insert

But if you use raw, bind the parameters.

madsem

5 years ago

Level 4

Thanks @jlrdw , the issue with those is that data is loaded application side, the example query i wrote in the first post doesn't require loading any results from the database, but it simply moves the data and updates each row.

Since I'm working with a lot of data, I can't use the standard methods.

The requirement is: select aggregated data, move data from one table to another, insert new & and update existing rows.

So it seems a raw query is needed eh?

Please or to participate in this conversation.