How to UPDATE .... FROM

[RonEskinder]

Im trying to build a query like this, any help appreciated

update tc_positions as t set
    speedlimit = c.speedlimit
from (values
    (1011827, 45),
    (1011831, 65)  
) as c(positionid, speedlimit) 
where c.positionid = t.id;

[enumag]

I assume you found this here? https://stackoverflow.com/a/18799497/512485 If this is correct syntax for postgres as the stackoverflow suggests then it should work as is. What happens when you run this query?

[RonEskinder]

yes, i tried that query and updated ok, i can see the values updated in the table

updated rows: 2

[enumag]

Oh so the issue is building it. Well prepared queries don't support array parameters so even the "simple" case of select ... where col IN (?) Is impossible. So you're basically forced to build the query yourself with php implode and such.

[RonEskinder]

ok, i already have something working with implode, but was looking for a nicer way. Thanks

[Tatikoma]

You can pass postgresql array and unnest them to row set.

This should be best way, due to this way u gonna use prepared statement and number of placeholders each time fixed.

update tc_positions as t set
    speedlimit = c.speedlimit
from 
    unnest(ARRAY[1011827, 1011831]::integer[], ARRAY[45, 64]::integer[])
 as c(positionid, speedlimit) 
where c.positionid = t.id;

This way you can replace arrays just with two placeholders and pass values as arrays.