SQL Updating from an Inner Join

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';

Update Query with INNER JOIN between tables in 2 different databases on 1 server

You could call it just style, but I prefer aliasing to improve readability.

UPDATE A    
  SET ControllingSalesRep = RA.SalesRepCode   
from DHE.dbo.tblAccounts A
  INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink RA
    ON A.AccountCode = RA.AccountCode

For MySQL

UPDATE DHE.dbo.tblAccounts A 
  INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink RA 
      ON A.AccountCode = RA.AccountCode 
SET A.ControllingSalesRep = RA.SalesRepCode

SQL UPDATE with INNER JOIN

remove the FROM products line.
and put the
SET ... line just before the WHERE clause.

to be clear :

UPDATE ...
JOIN ...
SET ...
WHERE ...

you could also do

UPDATE products p
SET p.product_price='87.00000'
WHERE EXISTS (SELECT NULL 
              FROM product_category pc
              WHERE p.product_id = pc.product_id
              AND pc.category_id = '64');

SQL update query syntax with inner join

The SET needs to come before the FROM\JOIN\WHERE portion of the query.

UPDATE CE
SET sJobNumber = AD.JobNumber
FROM CostEntry CE 
    INNER JOIN ActiveCostDetails As AD 
        ON CE.lUniqueID = AD.UniqueID
WHERE CE.SEmployeeCode = '002'
    AND SubString(CostCentre, 1, 1) = sDepartmentCode
    AND substring(CostCentre, 3, 1) = sCategoryCode
    AND substring(CostCentre, 5, 2) = sOperationCode

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;

sql update with inner join and where

Error 1064 is a MySQL syntax error. The correct MySQL syntax is:

UPDATE newsreactions nr INNER JOIN
       users u
       ON nr.memberId = u.id
    SET nr.enabled = 0
WHERE u.active =  0 AND u.comment LIKE '%spam%';

Notes:

• The JOIN goes in the UPDATE clause.
• Table aliases makes the query easier to write and to read.
• I am guessing that enabled and active are really numeric values. If so, do not use single quotes.

SQL update multiple table using inner join

UPDATE igrow.farm_management_batch b
INNER JOIN ( SELECT batch_id, SUM(actual_harvest) actual_harvest
             FROM igrow.farm_management_batchyield
             GROUP BY batch_id ) byl ON b.id = byl.batch_id
INNER JOIN igrow.sop_management_batchsopmanagement bsop ON b.id = bsop.batch_id

SET b.batch_status = 'completed', 
    b.stage = 'flowering', 
    b.actual_produce = byl.actual_harvest, 
    bsop.current_status='3' 

WHERE end_date < "2022-07-10 00:00:00.000000" 
-- and end_date is not null 
  AND b.batch_status IN ("running", "to_start")

end_date is not null is excess (if previous is true then this is true too), commented.

PS. There is no end_date column in shown tables - where it is taken from?

UPDATE with INNER JOIN on SQL Server

That's not the right syntax

UPDATE O 
SET O.UserName = U.UserName 
from Orders O
INNER JOIN Users U ON O.UserId = U.UserId ;

You need to start using Alias for tables references when there is more than one table involved in your query. Using Alias names makes the query more readable.

---

Related Topics