Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support MERGE
):
ANSI/ISO:
update ud
set assid = (
select sale.assid
from sale
where sale.udid = ud.id
)
where exists (
select *
from sale
where sale.udid = ud.id
);
MySQL:
update ud u
inner join sale s on
u.id = s.udid
set u.assid = s.assid
SQL Server:
update u
set u.assid = s.assid
from ud u
inner join sale s on
u.id = s.udid
PostgreSQL:
update ud
set assid = s.assid
from sale s
where ud.id = s.udid;
Note that the target table must not be repeated in the FROM
clause for Postgres. Main question: How to do an update + join in PostgreSQL?
Oracle:
update
(select
u.assid as new_assid,
s.assid as old_assid
from ud u
inner join sale s on
u.id = s.udid) up
set up.new_assid = up.old_assid
SQLite:
update ud
set assid = (
select sale.assid
from sale
where sale.udid = ud.id
)
where RowID in (
select RowID
from ud
where sale.udid = ud.id
);
SQLite 3.33 added support for an UPDATE
+ FROM
syntax analogous to the PostgreSQL one:
update ud
set assid = s.assid
from sale s
where ud.id = s.udid;
Main question: Update with Join in SQLite
Best Answer
You don't quite have SQL Server's proprietary
UPDATE FROM
syntax down. Also not sure why you needed to join on theCommonField
and also filter on it afterward. Try this:If you're doing something silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see here and here for more details:
The reason this is silly, is that you're going to have to re-run this entire update every single time any row in
table2
changes. ASUM
is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.