SQL Update using data from other tables and foreign keys

Question

I am trying to update a column from table1 based off of data from two other tables.

Table 1 has columns id, columnIWantToUpdate, table2FK, table3FK
Table 2 has columns table2FK, table2_unique_id
Table 3 has columns table3FK, table3_unique_id

So I get table2_unique_id and table3_unique_id as inputs and I want to use the columns table2FK and table3FK to update table 1 based off of the unique_ids I received as input

What have you tried so far? You could do Update with alias. `Update T1 Set T1.columnIWantToUpdate = T2.table2_unique_id from Table1 T1 inner join Table2 T2 on T2.table2FK = T1.table2FK` Look at this Question: https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server — Sebastian Siemens, May 14 '21 at 09:31
(1) Tag with the database you are using. (2) More clarify would be helpful. For instance, do conditions on both tables need to match or only one one? Sample data and desired results is almost always helpful. — Gordon Linoff, May 14 '21 at 10:11

score 0 · Answer 1 · answered May 14 '21 at 10:10

One method uses filtering in the where clause:

update table1
    set columnIWantToUpdate = ?
    where exists (select 1
                  from table2 t2
                  where t2.table2FK = table1.table2FK
                 ) or
          exists (select 1
                  from table3 t3
                  where t3.table3FK = table1.table3FK
                 );

It is not clear if you want and and or for the conditions.

SQL Update using data from other tables and foreign keys

1 Answers1