Update Query with Join on Two Tables

update query with join on two tables

this is Postgres UPDATE JOIN format:

UPDATE address 
SET cid = customers.id
FROM customers 
WHERE customers.id = address.id

Here's the other variations: http://mssql-to-postgresql.blogspot.com/2007/12/updates-in-postgresql-ms-sql-mysql.html

How to join two tables in an UPDATE statement?

You're on the right track, but you need to specify a JOIN between the tables:

UPDATE tweets JOIN users ON tweets.user_id = users.user_id
  SET tweets.spam = 1
WHERE users.name = 'SUSPENDED'

Update multiple tables in SQL Server using INNER JOIN

You can't update more that one table in a single statement, however the error message you get is because of the aliases, you could try this :

BEGIN TRANSACTION

update A
set A.ORG_NAME =  @ORG_NAME
from table1 A inner join table2 B
on B.ORG_ID = A.ORG_ID
and A.ORG_ID = @ORG_ID

update B
set B.REF_NAME = @REF_NAME
from table2 B inner join table1 A
    on B.ORG_ID = A.ORG_ID
    and A.ORG_ID = @ORG_ID

COMMIT

SQL Update after Joining Two Tables

You don't need to use a LEFT JOIN here, since you are checking on a condition from table 2, so an INNER JOIN should be better here.

UPDATE T1
SET T1.BitToUpdate = 1
FROM myTable1 T1
INNER JOIN myTable2 T2
    ON T1.MyId = T2.MyId
WHERE T2.BitToCheck = 1

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

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

UPDATE statement with multiple joins to main table in PostgreSQL

As documented in the manual you should not repeat the target table in the FROM clause of an UPDATE statement.

The FROM clause for an UPDATE unfortunately doesn't follow the exact same rules as the one used in the SELECT clause. It's easier to use the old implicit joins instead of the (usually preferred) explicit JOIN operators.

As far as I can tell this is what you are looking for:

UPDATE eutmpdfhdt as t
  SET nwcolid = t2.nwcolid
FROM eutmpdfh t1, 
     eutmptbldt t2 
WHERE t.dfhid = t1.dfhid 
  AND t.colid = t2.colid  
  AND t1.dfhtyp IN ('D','U','S','P','B')

Update column by join two tables using Oracle

In oracle, Update using JOIN is not allowed.

You can use MERGE. It can update one table using another table (s).

MERGE INTO T1
USING (SELECT T2.PRODUCT,
              T2.ORDER
         FROM T2 WHERE T2.PRODUCT LIKE '%P12%') T2 
ON ( T1.ORDER = T2.ORDER )
WHEN MATCHED THEN UPDATE
   SET T1.USER = T2.PRODUCT

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

---

Related Topics