Update a Table with Join

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 a table with join?

It is not possible to do this with a JOIN. The Firebird UPDATE statement has no FROM clause. The syntax is:

UPDATE {tablename | viewname} [[AS] alias]
   SET col = newval [, col = newval ...]
   [WHERE {search-conditions | CURRENT OF cursorname}]
   [PLAN plan_items]
   [ORDER BY sort_items]
   [ROWS <m> [TO <n>]]
   [RETURNING <values>]

<m>, <n>     ::=  Any expression evaluating to an integer.
<values>     ::=  value_expression [, value_expression ...]
<variables>  ::=  :varname [, :varname ...]

However the equivalent of your example query is:

update INVOICE_ITEMS 
set DISCOUNT = 3 
WHERE EXISTS (SELECT 1 FROM ITEM_PRICE_QUNTITY B WHERE B.ID = ITEM_PRICE_NO)
AND INVOICE_ID = 33

If you want to update using data from additional tables, you might want to consider using MERGE. In your comment you ask for the equivalent query to do the following with Firebird:

UPDATE B 
SET B.QUANTIY = b.QUANTIY + a.QUANTITY 
FROM ITEM_PRICE_QUNTITY B JOIN INVOICE_ITEMS A ON A.ITEM_PRICE_NO = B.ID 
WHERE A.INVOICE_ID = 33

The equivalent MERGE statement would be:

MERGE INTO ITEM_PRICE_QUNTITY AS B
    USING INVOICE_ITEMS AS A
    ON A.ITEM_PRICE_NO = B.ID AND A.INVOICE_ID = 33
    WHEN MATCHED THEN
        UPDATE SET B.QUANTIY = B.QUANTIY + A.QUANTITY 

How can I do three table JOINs in an UPDATE query?

The answer is yes, you can.

Try it like this:

UPDATE TABLE_A a
    JOIN TABLE_B b ON a.join_col = b.join_col AND a.column_a = b.column_b
    JOIN TABLE_C c ON [condition]
SET a.column_c = a.column_c + 1

For a general update join:

UPDATE TABLEA a
JOIN TABLEB b ON a.join_colA = b.join_colB
SET a.columnToUpdate = [something]

Update with join with BigQuery

UPDATE `pfam31.uniprot` a
SET a.auto_architecture = b.auto_architecture
FROM `pfam31.uniprot_architecture` b
WHERE a.uniprot_acc = b.uniprot_acc

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;

Updating two tables with an inner join

For MySQL UPDATE with JOIN syntax is different, the SET part should come after the JOIN

Use the following query to update the entries:

UPDATE question q
INNER JOIN answer a ON a.answer_id = q.answer_id
SET q.question = 'dmvvnnv'
    ,a.comment = 'covonfvk'
    ,a.rating = 5
WHERE a.doctor_id = 8

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