How to update specific rows using laravel

Question

I have a table:

ID|user_id|group_id|subject |book_id|duplicate
 1| 2     |3       |history |1      |         
 2| 4     |3       |history |1      |
 3| 5     |3       |history |1      |

I want the resulting table to look like this:

ID|user_id|group_id|subject |book_id|duplicate
 1| 2     |3       |history |1      |         
 2| 4     |3       |history |1      |1
 3| 5     |3       |history |1      |1

I want all ascending IDs after the lowest ID duplicate column to be updated to 1. PLEASE NOTE: the IDs are dynamic and so simply using ->where(ID, '>', 1); will not work in all cases.

so far I have this

$duplicates = DB::table('table')
               ->where('subject', 'history')
               ->where('book_id', 1)
             ->skip(1)->take(1)
            ->update(['duplicate' => 1]);

The code above does not work because I get a resulting table that looks like this:

ID|user_id|group_id|subject |book_id|duplicate
 1| 2     |3       |history |1      | 1        
 2| 4     |3       |history |1      |
 3| 5     |3       |history |1      |

Have you even bothered to read the Laravel documentation? laravel.com/docs/master/eloquent#basic-updates — Martin Bean
– Martin Bean, Commented Jul 14, 2015 at 14:29

Javi Stolz · Accepted Answer · 2015-07-14 14:19:07Z

2

Use this

DB::table('table')
->where('subject', 'history')
->where('book_id', 1)
->where('id', '>', 1)
->update(['duplicate' => 1]);

answered Jul 14, 2015 at 14:19

Javi Stolz

4,7531 gold badge32 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Billy Over a year ago

your answer is correct however I've edited the question. Sorry I forgot to mention that ID is dynamic.

Javi Stolz Over a year ago

Then replace 1 with the variable that stores the dynamic value. i.e: ->where('id', '>', $dynamicId)

nCrazed · Accepted Answer · 2015-07-15 11:45:28Z

0

You are trying to apply an offset to an UPDATE query, which is not a valid SQL (I am assuming that the query builder silently ignores your skip(1)->take(1) call when update() is called.)

One way to achieve the result you're looking for is to find all ids that should be marked as non-duplicate (duplicate = null) and then update every other relevant record.

Concrete book

$original = DB::table('table')
    ->select('id')
    ->where('subject', 'history')->where('book_id', 1)
    ->orderBy('id', 'asc')->first()->pluck('id'); // Lowest id

DB::table('table')
    ->where('subject', 'history')->where('book_id', 1)
    ->whereNot('id', $original) // All, but one
    ->update(['duplicate' => 1]);

All Books

$originals = DB::table('table')
    // One for every combination of `subject` and `book_id`
    ->groupBy('subject', 'book_id')>orderBy('id', 'asc') 
    ->lists('id');

DB::table('table')
    ->whereNotIn('id', $originals) // All, but one for every book
    ->update(['duplicate' => 1]);

You can further optimise either example to employ a sub-query instead two separate queries.

edited Jul 15, 2015 at 11:45

answered Jul 14, 2015 at 19:45

nCrazed

1,0356 silver badges20 bronze badges

2 Comments

Billy Over a year ago

Thanks but I don't want to update every relevant record that does not match non-duplicates. I want to update all but one.

nCrazed Over a year ago

@Billy, I wrote the initial answer in a hurry which resulted in code that didn't do what I intended it to do. I've updated it now with clearer phrasing as well as correct code.

Collectives™ on Stack Overflow

How to update specific rows using laravel

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related