postgres UPDATE - FROM syntax

[snowcxt]

Is there a way to write postgres script like this?

UPDATE vehicles_vehicle AS v 
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id 

[jurko-gospodnetic]

If you have that as a string in JS, just execute it using knex.raw().

I don't think you can do it using knex's query building functions (though I could be mistaken :-)), at least because it would require specifying two main tables in the query, and knex's query objects support only one.

You could always implement such an UPDATE using a sub-query instead of using the UPDATE/FROM SQL extension (which even gets implemented with different semantics by different databases), e.g. (warning: untested code :-)):

var subQuery = knex
    .select('s.price_per_vehicle')
    .from('shipments_shipment AS s')
    .where('s.id', knex.raw('v.shipment_id'))
;
knex.update({price: subQuery}).table('vehicles_vehicle AS v');

[snowcxt]

Thank you. But could you show me how to turn this script into knex query without raw or whereIn?

update "transaction"
set "status" = 'completed'
from "return_transaction"
where "return_transaction"."id" = 1 and "transaction"."id" = "return_transaction"."transaction"

[elhigu]

I think Jurko just explained that knex cannot build you query like you are writing without knex.raw and I is there any reason why you like to avoid whereIn?

This is might work though

var subQuery = knex('transaction')
  .select('transaction.id')
  .join('return_transaction', 'transaction.id', '=', 'return_transaction.transaction')
  .where('return_transaction.id', 1);

knex('transaction').update({'status' : 'completed'}).where('id', subQuery);

I suppose that whereIn is not required in this case, since I assume that there is unique constraint for return_transaction.id, which effectively causes subQuery to always return 0 or 1 results... I would use whereIn though... code above might work or it might not. I didn't test it.

[snowcxt]

No it's not working. I get error column reference "id" is ambiguous

I believe knex should support "UPDATE - FROM" syntax for postgres.

[elhigu]

I updated subquery there was ambiguous column identifier in .select. You still didn't mention why you like to avoid whereIn?

Yep, that would be nice if update - from could be supported. If you decide to implement it, pull requests are always welcome.

[onzag]

I don't understand how that makes for two main tables, you just need to add an "updateFrom" that simply adds a "from tableName" after the set, it's a simple string.

Another option would be to add an options to the update method.

this.knex.table("table1").update({id: 2}, {from: ["table2"]})

Or

this.knex.table("table1").update({id: 2}).updateFrom(["table2"]);

[onzag]

I have implemented a this.knex.table("table1").update({id: 2}).updateFrom("table2", "alias2").updateFrom("table3", "alias1"); solution, if anyone can tell me what should be done for testing, since this is pgsql specific and seems like it's hard of a thing to test as I can't get my head around these tests.

I was thinking to use the update options but there are so many overloads I found it bloated.

[ffxsam]

@onzag Do you still have the code for your .updateFrom() method? I'd love to have it.

[onzag]

@ffxsam sadly not anymore, I ended up moving away from knex and wrote my own wrapper on top of pg as I found more limitations, I never uploaded that change anywhere.

[ffxsam]

@onzag No worries, thanks for responding!

[wms]

I have a PR open (#5386) that implements this.

[OlivierCavadenti]

Released in 2.5.0