[SOLVED] Update table getting values from three joining tables

Update table getting values from three joining tables

I was trying to run an update for the column that belongs to the table INVOICE with the data of another table RECEIPT e.g. INVOICE.field1 = RECEIPT.field1, however there is no direct relationship between INVOICE and RECEIPT, but by the PURCHASE table that contains relations with both tables. I am using SQL Server 2022.

I tried below option but I have syntax errors.

UPDATE INVOICE T1
SET T1.field1 = T3.field1
FROM PURCHASE T2 
INNER JOIN RECEIPT T3 ON T2.field2 = T3.field2 
WHERE T1.field3 = T2.field3 
  AND T1.field1 IN ('x')

UPDATE INVOICE T1
JOIN PURCHASE T2 ON T2.field2 = T3.field2 
JOIN RECEIPT T3 ON T1.field3 = T2.field3
SET T1.field1 = T3.field1
WHERE T1.field1 IN ('x')

UPDATE INVOICE T1, PURCHASE T2, RECEIPT T3 
SET T1.field1 = T3.field1
WHERE T1.field3 = T2.field3 
  AND T2.field2 = T3.field2
  AND T1.field1 IN ('x')

Solution

I prefer to use the following syntax:

UPDATE t /*alias of the table to update*/
SET someField = t2.someOtherField
FROM TABLE1 t /*the table with the above alias */
INNER JOIN TABLE3 t3 
   ON t3.ID = t.ID
INNER JOIN TABLE2 t2
   ON t2.ID = t3.ANOTHER_ID

This way you can build your joins as needed and just refer to correct table in the beginning of the UPDATE.