SQL Update Query Syntax with Inner Join

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

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

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

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 query syntax error, UPDATE statement with INNER JOIN

This is valid syntax:

UPDATE tblMitarbeiterUUID x

JOIN arbeiter y
ON x.idMitarbeiterUUID = y.fidMitarbeiterUUID 

SET x.dtPassword="A"  

WHERE y.id=1

R SQL Update statement with Inner Join

There are several problems:

1. One line of code in the question sets IncomeRider to 'N' and another to 'Y'. We assume you meant 'Y'.

2. sqlite does not support joins in update (but it does support correlated subqueries). For the syntax of update in sqlite see: https://www.sqlite.org/lang_update.html

3. The code shown in the question is trying to update NewData but IncomeRider is a column in Inforce, not NewData.

4. update does not return a result -- it only performs an update of the table in sqlite -- so a select is needed in the sqldf argument to return something.

Addressing these we have:

sqldf(c("update Inforce
         set IncomeRider = 'Y' 
         where fileDate >= (select n.RiderTerminationDate 
                            from NewData n
                            where n.ContractNumber = Inforce.PolicyNumber)",
         "select * from Inforce"))

giving:

  PolicyNumber IncomeRider   FileDate
1            1           Y 2014-01-01
2            2           N 2013-01-01
3            3           Y 2016-08-01
4            4           N 2016-02-01

The sqldf statement returns the updated Inforce but you still have to assign that to Infoce or some other variable: Inforce_updated <- sqldf(...)

2) The poster of the question also posted an answer although it gives a different result than asked for in the question. In that answer instead of performing an update to a subset of rows in Inforce a merged data frame is returned in which the existing IncomeRider column is completely ignored and it is set to N unless the required condition is satisfied. If that is what is wanted then try this:

sqldf("select PolicyNumber, 
              case when RiderTerminationDate is null or 
                        FileDate >= RiderTerminationDate 
              then
                   'N'
              else 
                   'Y'
              end IncomeRider,
              FileDate,
              RiderTerminationDate
       from Inforce 
       left join NewData on ContractNumber = PolicyNumber")

giving:

  PolicyNumber IncomeRider   FileDate RiderTerminationDate
1            1           Y 2014-01-01           2015-01-01
2            2           Y 2013-01-01           2015-06-01
3            3           N 2016-08-01           2016-05-01
4            4           N 2016-02-01                 <NA>

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 ...

---

Related Topics