Level 60
does songboxes table have user_id column in it?
Aug 15, 2020
7
Level 5
Insert into... query problem
Sorry but this is a general MySQL question and not strictly Laravel related. But I wouldn't ask anywhere else as this community is the best.
I'm trying to INSERT INTO by using this query
INSERT INTO songboxes (plays)
select count(`songbox_id`)
from activity_updates as au
join songboxes on au.songbox_id = songboxes.token
where `status` = 1
Group by songbox_id
I am getting the error
Field 'user_id' doesn't have a default value
But I'm not trying to do anything with the user ID, as you can see from the query.
I think the problem may lie in that not every row in the destination table needs updated. However... maybe that's not the problem at all. I really am quite stuck.
Anyone have any suggestions?
Level 5
Hey thanks for the super quick reply.
Yes it does. They both have a user_id column.
Level 60
Here's the problem I guess, you try to insert in a table without completing all the fields, user_id is required,
INSERT INTO songboxes (plays, user_id)
or you could make it nullable if it's the case, or even default value, idk
Level 5
User_id is already set on the other table though. I’m only trying to update the one column plays - which is a new column.
Level 5
I ended up just doing it in PHP:
// Get the values from table one that I need
$activity_update = DB::table('activity_updates')
->select(DB::raw('songbox_id, count(songbox_id) as count'))
->where('status', '=', 1)
->groupBy('songbox_id')
->get();
// Loop through the values, check if the row exists then update it.
foreach ($activity_update as $update) {
$check = Songbox::query()->where('token', '=', $update->songbox_id)->exists();
if ($check) {
$s = Songbox::query()->where('token', '=', $update->songbox_id)->first();
$s->plays = $update->count;
$s->save();
}
}
Level 60
$s = Songbox::query()->where('token', '=', $update->songbox_id)->first();
$s->plays = $update->count;
$s->save()
this stuff is an update statement, not insert .. i wanted to ask but..
Level 5
Oh my god!!
you're right. Jesus Jones!!
It was in fact an update I was wanting to do. Honestly...... 🤪
Please or to participate in this conversation.