CODEWITHSUNDEEP
laraveleloquent
Billy
Billy

Reputation: 2963

How to update specific rows using laravel

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      |

Upvotes: 1

Views: 2334

Answers (2)

nCrazed
nCrazed

Reputation: 1035

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.

Upvotes: 0

Javi Stolz
Javi Stolz

Reputation: 4753

Use this

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

Upvotes: 2

Related Questions