Laravel raw SQL update query
1 min read

Laravel raw SQL update query

Using a raw SQL query in Laravel using Eloquent ORM

A really small mistake - I wanted to run a MySQL update statement on laravel (no bindings, no parameters, no models) so I wrote this

DB::update(`UPDATE table SET something = 1 WHERE id = 1`);

When I ran this, I got this error

ValueError: PDO::prepare(): Argument #1 ($query) cannot be empty

Was a bit surprised to see this. Upon reading the documentation, you're supposed to run the query this way, for queries not returning any values

DB::statement('UPDATE table SET something = 1 WHERE id = 1');

Also, I had used backticks `` for the original longer SQL statement so that seemed to have caused on issue.

Alternatively, I could do it without using eloquent models like:

DB::table('users')->where('id', '1')->update(['something' => 1]);

If you do that and want to get the raw SQL query, you can use the toSql method:

DB::table('users')->where('id', '1')->toSql(); 

// gives a query like
select * from `users` where `id` = ?

There is a ? in there in the query because the values are parameterized. You can use dump method on it instead to get the query with bindings as well:

DB::table('users')->where('id', '1')->dump(); 

// result

select * from `users` where `id` = ? // vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:3788
array:1 [ // vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:3788
  0 => "11111111"

If you try to print or dump a SQL statement on an update query, it will throw an error as query has already executed and it returns the number of rows that were affected by the update query.

DB::table('users')->where('id', '1')->update(['id' => 1])->toSql();
Error  Call to a member function toSql() on int.

To get around this, you can use the query log. One downside is that this will execute the query and then get you the log.


DB::table('users')->where('id', '1')->update(['id' => 1])->toSql();