Update table with joined column from other table

I have a working SQL query that I can't translate into a Eloquent Expression :

UPDATE
	tableA
SET
	propInTableA = tableB.propInTableB * 100
FROM
	tableB
WHERE
	tableA.product_id = tableB.id

Right now, I made it work by wrapping the request within a DB::update() statement, but was wondering if I could use a more expressive Eloquent syntax (with something like TableA::update(...)

To explain, what this query is doing, is updating a property in table1 from a value in tableB. They key is the FROM clause that is required to make it work, which I'm having a hard time transcribing in Eloquent (using ->from won't work as it will build a query that won't have any sense)

PS: This is the post that helped me figured how to actually do this kind of thing in pure SQL : https://www.postgresqltutorial.com/postgresql-update-join/

tykus

5 years ago

Level 104

You can use a raw expression:

TableA::join('table_b', 'table_b.id', '=', 'table_a. product_id')
  ->update(['table_a. propInTableA' => DB::raw('table_b.propInTableB * 100')]);

(Untested, but it should work)

beNjiox

5 years ago

Level 13

Hey @tykus -

Thanks for the answer but this doesn't work. I should have mentionned it in my original message, but we did try something like that.

The SQL request generated from this is really not what you would expect it to be.

UPDATE
	"table_a"
SET
	"propInTableA" = table_b.propInTableB * 100
WHERE
	"ctid" in(
		SELECT
			"table_a"."ctid" FROM "table_a"
			INNER JOIN "table_b" ON "table_b"."id" = "table_a"."product_id"
)

And it generates the following error message

SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table "table_b"

FYI : I'm redacting & simplifying the fields/table names, but we do have a use case for this in a production app.

1 like

Insomniak

1 year ago

Level 1

Hi @beNjiox, did you find a solution for this or did you just go to raw statement ?

edit: found my own answer, what we need is updateFrom($values) and not just update($values)

TableA::join('table_b', 'table_b.id', '=', 'table_a. product_id') ->updateFrom(['table_a. propInTableA' => DB::raw('table_b.propInTableB * 100')]);

Please or to participate in this conversation.