How can I do an UPDATE statement with JOIN in SQL Server?
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.
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
);
SQL update query using joins
UPDATE im
SET mf_item_number = gm.SKU --etc
FROM item_master im
JOIN group_master gm
ON im.sku = gm.sku
JOIN Manufacturer_Master mm
ON gm.ManufacturerID = mm.ManufacturerID
WHERE im.mf_item_number like 'STA%' AND
gm.manufacturerID = 34
To make it clear... The UPDATE
clause can refer to an table alias specified in the FROM
clause. So im
in this case is valid
Generic example
UPDATE A
SET foo = B.bar
FROM TableA A
JOIN TableB B
ON A.col1 = B.colx
WHERE ...
Update a table using JOIN in SQL Server?
You don't quite have SQL Server's proprietary UPDATE FROM
syntax down. Also not sure why you needed to join on the CommonField
and also filter on it afterward. Try this:
UPDATE t1
SET t1.CalculatedColumn = t2.[Calculated Column]
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2
ON t1.CommonField = t2.[Common Field]
WHERE t1.BatchNo = '110';
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:
;WITH t2 AS
(
SELECT [key], CalculatedColumn = SUM(some_column)
FROM dbo.table2
GROUP BY [key]
)
UPDATE t1
SET t1.CalculatedColumn = t2.CalculatedColumn
FROM dbo.table1 AS t1
INNER JOIN t2
ON t1.[key] = t2.[key];
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. A SUM
is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.
UPDATE statement with JOIN in SQL Server Not Working as Expected
I suspect this does what you want:
update v
set last_inspection_fw = (
select max(j.job_date_fw)
from job_headers_fw j
inner join job_details_fw jd on jd.job_number_fw = j.job_number_fw
where j.vehicle_id_fw = v.vehicle_id_fw and jd.reason_code_fw = 35
)
from vehicles_fw v
SQL Server - Using joins in Update statement
You could use EXISTS
and a correlated subquery to filter on rows whose UserId
has at least one audit event of id 14
:
UPDATE h
SET IsActivated = 1
FROM [dbo].HRUser h
WHERE EXISTS (
SELECT 1 FROM
FROM dbo.[UserAudit] a
WHERE a.UserId = h.UserId AND a.AuditTypeId = 14
)
Note that there is no point reopening the target table in the subquery; you just need to correlate it with the outer query.
How to do a sql update statement with a join?
If you are using sql-server you can use this script.
update customer_ar
set current_bal =
(select sum(current_balance)
from ar_transaction T1
where T1.customer_id =customer_ar.customer_id)
where customer_id in (1502,1329,3096,1516,3605);
SQL Server - inner join when updating
UPDATE R
SET R.status = '0'
FROM dbo.ProductReviews AS R
INNER JOIN dbo.products AS P
ON R.pid = P.id
WHERE R.id = '17190'
AND P.shopkeeper = '89137';
How to use an UPDATE Query with an INNER JOIN to update fields within a table
Your syntax is indeed incorrect for SQL Server - if I understand your last paragraph you just need a conditional case expression. If the following (of course untested) is not correct hopefully it's enough to put you on the right track:
update t1 set t1.Marked =
case t2.type
when 'Summary' then 'Yes'
when 'Full' then 'No'
else 'N/A'
end
from tbl_1 t1
left join tbl_2 t2 on t1.PersNo = t2.PersNo;
Related Topics
Return Row Only If Value Doesn't Exist
Solutions For Insert or Update on SQL Server
Dynamic Alternative to Pivot With Case and Group By
Any Downsides of Using Data Type "Text" For Storing Strings
How to Get Column Names from a Table in SQL Server
How to Perform Grouped Ranking in MySQL
Delete from Two Tables in One Query
Query For Array Elements Inside Json Type
How Can Prepared Statements Protect from SQL Injection Attacks
Is Select or Insert in a Function Prone to Race Conditions
SQL Statement Is Ignoring Where Parameter
MySQL Cannot Add Foreign Key Constraint