Reputation: 2963
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
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
Reputation: 4753
Use this
DB::table('table')
->where('subject', 'history')
->where('book_id', 1)
->where('id', '>', 1)
->update(['duplicate' => 1]);
Upvotes: 2