The 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 ] [, ...] ]
In your case I think you want this:
UPDATE vehicles_vehicle AS v
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id
Or if you need to join on two or more tables:
UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
t2.id = t1.t2_id
AND t3.bar = True;
Answer from Mark Byers on Stack OverflowThe 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 ] [, ...] ]
In your case I think you want this:
UPDATE vehicles_vehicle AS v
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id
Or if you need to join on two or more tables:
UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
t2.id = t1.t2_id
AND t3.bar = True;
The answer of Mark Byers is the optimal in this situation. Though in more complex situations you can take the select query that returns rowids and calculated values and attach it to the update query like this:
with t as (
-- Any generic query which returns rowid and corresponding calculated values
select t1.id as rowid, f(t2, t2) as calculatedvalue
from table1 as t1
join table2 as t2 on t2.referenceid = t1.id
)
update table1
set value = t.calculatedvalue
from t
where id = t.rowid
This approach lets you develop and test your select query and in two steps convert it to the update query.
So in your case the result query will be:
with t as (
select v.id as rowid, s.price_per_vehicle as calculatedvalue
from vehicles_vehicle v
join shipments_shipment s on v.shipment_id = s.id
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid
Note that column aliases are mandatory otherwise PostgreSQL will complain about the ambiguity of the column names.
PostgreSQL update join vs SQL Server update join - Database Administrators Stack Exchange
Update with inner join
help with query to update two columns on two tables
Occasional very slow simple UPDATE on a single row
But running in Postgres, the query is glacially slow.
UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2 ON t1.id = t2.id;
There is no join condition between foo and t1, the implicit CROSS JOIN forces a Cartesian product, i.e. O(N²) (!) update operations instead of just O(N). And the result is non-deterministic nonsense. The effect also becomes apparent in the query plan: rows=338326628280 instead of rows=581621 (Also: both plans were produced off slightly different tables, but that seems irrelevant to the question.)
Could be fixed by adding a join condition like:
UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2 ON t1.id = t2.id
WHERE foo.id = t1.id; -- !
Well, technically, a WHERE condition, but all the same.
But that's just putting lipstick on a pig. While id is the PK column of each table, that's just adding noise. Use the command you already found instead:
UPDATE foo
SET bar = t2.bar
FROM foo2 t2
WHERE foo.id = t2.id;
The manual advises for the FROM clause of UPDATE:
Do not repeat the target table as a
from_itemunless you intend a self-join (in which case it must appear with an alias in thefrom_item).
And:
When a
FROMclause is present, what essentially happens is that the target table is joined to the tables mentioned in thefrom_itemlist, and each output row of the join represents an update operation for the target table. When usingFROMyou should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.
Such a self-join makes sense (or is even necessary!) if you need a LEFT [OUTER] JOIN to additional table(s). Sadly, there is no provision in SQL to say "FROM LEFT" in an UPDATE. Example:
- Nullify column in update if subquery returns empty
I want to address part of the question from a SQL Server perspective.
In T-SQL I would do an update using a join using something like this:
UPDATE foo SET bar = t2.bar FROM foo t1 JOIN foo2 t2 ON t1.id = t2.id;
Maybe something like that, but probably not exactly that. It is too easy to end up with an accidental cross join. The SQL Server documentation doesn't really talk about that, but it should because this trips people up all the time.
Algebrization of UPDATE...FROM can be quirky (often for backward-compatibility), so it is important to be explicit and follow best practice. This means:
- Assigning an alias
- Updating that alias; and
- Ensuring the update is deterministic i.e. each target row is updated from at most one source row.
Extracts from the same documentation page:
If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias.
Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.
and, revealing some of the algebrization issues:
When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Unambiguous CTE references are required because a CTE does not have an object ID, which SQL Server uses to recognize the implicit relationship between an object and its alias. Without this relationship, the query plan may produce unexpected join behavior and unintended query results.
So the T-SQL example would be:
CREATE TABLE dbo.foo (
id INTEGER NOT NULL PRIMARY KEY,
bar INTEGER NULL
);
CREATE TABLE dbo.foo2 (
id INTEGER NOT NULL PRIMARY KEY,
bar INTEGER NULL
);
-- Update the alias
UPDATE F1
SET F1.bar = F2.bar
FROM dbo.foo AS F1
JOIN dbo.foo2 AS F2
ON F2.id = F1.id -- deterministic;
One way to check an update is deterministic in SQL Server is to first write it as a MERGE, which does check for non-deterministic updates at runtime. You would not routinely write a simple update as a merge for performance reasons (and maybe because merge has a few issues of its own).
Guys, Im trying to update using another table data as reference to set a column of the first table when the 'where' conditions being completed, but it's still updating the whole table, Im just lost right now, can anyone help ? This is my code:
update movimento_venda
set situacao = 2
from movimento_venda mv
inner join venda v on(v.id_movimento_venda = mv.id_movimento_venda)
where v.numero_cupom = 946431
and v.total_venda = 10.25
and cast(v.data_cupom as date) = '20221107'