SQL Update Query Using Joins

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

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

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

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.

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;

update query using join

Check out the syntax of the UPDATE statement in Informix. The join notation updates are not described, which is why you get errors when you attempt to use it.

You will need to use an UPDATE statement of the general form:

UPDATE device
   SET name = 'SME-IT' -- Or did you mean CME-IT?  The question says both
 WHERE pkid IN (SELECT ...)

and the ... part will be a query that identifies the primary key (or some other candidate (unique) key) of the rows in the Device table for which the current device name is TP-424-RL and the rows should be updated. (I'm assuming that it isn't as simple as 'all rows where the device name is TP-424-RL should be updated.)

A trivial version might be:

UPDATE device
   SET name = 'SME-IT' -- Or did you mean CME-IT?  The question says both
 WHERE pkid IN
      (SELECT d.pkid
        FROM numplan AS n 
        JOIN routepartition AS rp 
          ON rp.pkid = n.fkroutepartition 
        JOIN devicenumplanmap AS dnpm 
          ON dnpm.fknumplan = n.pkid 
        JOIN device AS d
          ON dnpm.fkdevice = d.pkid 
        JOIN routelist AS rl 
          ON rl.fkdevice = d.pkid 
        JOIN routegroup AS rg 
          ON rg.pkid = rl.fkroutegroup AND n.tkpatternusage = 5 
       WHERE d.name='TP-424-RL'
      )

My suspicion is that the query can be simplified, but I'm not sure of all the details of your schema. The AND n.tkpatternusage = 5 condition can be moved around; it seems to belong most logically to the WHERE clause of the SELECT, or perhaps the ON condition joining n to rp or dnpm. The optimizer will move it to where it provides most bang for the buck, which is likely to be filtering on the scan of the numplan (aka n) table.

---

Related Topics