Laravel eloquent update join with DB::raw() unknown column

Question

I'm trying to run this update statement, but I get this error:

SQLSTATE[HY000]: General error: 1 no such column: table2.id

DB::table('table1')
            ->join('table2', 'table1.row_hash', '=', 'table2.row_hash')
            ->where('table1.some_column', '=', 0)
            ->whereNull('table1.reference_no')
            ->update([
                'table1.column_to_update' => 1,
                'table1.column_to_update_2' => 1,
                'table1.column_to_update_3' => 1,
                'table1.reference_no' => DB::raw('table2.id') <--comment this line out and it works.
            ]);

If I comment out that one column from the update statement it works. I've tried using various combinations of quotes and backticks inside of the DB::raw() statement, but still get the same error. This post seems to indicate that I'm doing this the right way, but it's not cooperating.

How can I update the value of table1.reference_no to the value of table2.id? I was hoping to accomplish this in one eloquent query as it's a pretty basic SQL statement. Unfortunately I've also tried using just a raw SQL statement, which yielded other errors despite working when running it directly in my mysql client. This is taking entirely too much time for how simple it should be.

Your are executing your MySQL update behind an obfuscation layer. If you can, try to print the current (failing) update query to a log file, and see what the syntax problem is. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 19, 2022 at 12:38
Did you mean to put backticks in the whereNull call? That's the execution operator, so probably returning null. — benJ
– benJ, Commented Nov 19, 2022 at 16:11
And, this may be obvious, but does table2 / quickbooks_transactions have an id column? — benJ
– benJ, Commented Nov 19, 2022 at 16:12
@benJ - Sorry, backticks were unintentional, that wasn't causing the error. I updated the post. table2 does have an ID column.. — hyphen
– hyphen, Commented Nov 20, 2022 at 15:50

gammelbert · Accepted Answer · 2023-02-11 15:26:03Z

1

Just stumbled over this.

Putting the values in backticks works. Like this:

'table1.reference_no' => DB::raw('`table2`.`id`')

answered Feb 11, 2023 at 15:26

gammelbert

112 bronze badges

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

Collectives™ on Stack Overflow

Laravel eloquent update join with DB::raw() unknown column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related