Update Multiple Tables in SQL Server Using Inner Join

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

Update multiple table with inner join

UPDATE pur_order a, pur_entry b SET a.is_deleted = 1, b.is_deleted = 1
WHERE b.id_pur_order = b.id AND a.id = 1;

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

SQL - Update Multiple Tables, Where Inner Join Value is from Third Table

The JOIN is part of the UPDATE statement, not after the SET. So you might try:

UPDATE catalog_product_entity_decimal cped JOIN
       catalog_product_entity cpe
       ON cpe.entity_id = cped.entity_id JOIN
       cataloginventory_stock_item csi
       ON csi.entity_id = cpe.entity_id
    SET cped.value = 37.95,                                                          
        csi.qty = 4
    WHERE cpe.sku = '094922562333';

Notes:

• The JOINs are in the UPDATE clause.
• Table aliases make the query easier to write and to read.
• You need a JOIN for cataloginventory_stock_item.
• Single quotes are not required around numbers.

Update columns in multiple tables with inner join

H2 does not support updating two tables at the same time within one SQL statement. You would need to use two statements. For the supported syntax, see the UPDATE statement railroad diagram.

updating a table by joining multiple tables

Simply replace 'SELECT...FROM' with 'UPDATE' and add a 'SET ...' clause before WHERE:

UPDATE catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
LEFT JOIN catalog_lu_topics e ON a.topicID = e.topicID
SET d.catalogFileID = 'cfil123',
    b.catalogFileID = 'cfil123'
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND e.parentID = 'top305'
AND a.sortorder =1
AND e.topicID = 'top318'

Make sure you specify which tables' fields to update in the SET clause using <table>.<field> notation.

Edit: Removed extra comma ...

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

---

Related Topics