CODEWITHSUNDEEP
postgresqlpostgresql-9.4
Max
Max

Reputation: 538

Update with inner join Postgresql

Because doing this update does not work for the where clause? The update does it for me all.

UPDATE ventas SET eav_id = 7 
FROM ventas AS A
inner join ventasDetalle AS e on A.act_id = e.act_id and e.exp_id = A.exp_id
where a.eav_id = 1

Upvotes: 4

Views: 14055

Answers (2)

Billy Ferguson
Billy Ferguson

Reputation: 1439

The Postgresql UPDATE syntax is:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

So I think you want:

UPDATE ventas AS A 
SET eav_id = 7
FROM ventasDetalle AS e
WHERE (A.act_id = e.act_id and e.exp_id = A.exp_id)

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

update ventas a
set eav_id = 7 
from ventasDetalle e
where a.eav_id = 1 and (a.act_id, a.exp_id) = (e.act_id, e.exp_id)

Upvotes: 8

Related Questions