For analysis, we need to integrate some data into a table, which involves associating multiple tables with the primary key to obtain the values of some of the fields. We can simply implement it through update. I found an article, pretty good:
When updating tables in batches, when updating columns in a table, you must depend on another table. This dependency can be a where Condition Clause or the field value to be updated depends on another table.
There are usually two methods
1. Use stored procedures
2. Execute them one by one in the program code
Here we provide a more efficient and concise way to update SQL statements in batches. An SQL statement can replace the troublesome loop process, which can be written in MS sqlserver, Oracle, and DB2.
-- Key point: T4 and T1 are the same table, and primary key must be the same,
-- And associate it with it so that fields of the table to be updated can be referenced in the SELECT statement.
Update Table1 as T1
Set (field1, field2) = (select field21, field22
From Table2 T2
Inner join table3 T3
On t3.field31 = t2.field23
Inner join table4 T4
On t3.field32 = t4.filed41
Where t2.field24> =''
And t1.fid = t4.fid );
---------------------------- Ms sqlserver --------------------------------------
Update T1
Set field1 = field21, field2 = field22
From Table2 T2
Inner join table3 T3
On t3.field31 = t2.field23
Inner join table4 T4
On t3.field32 = t4.filed41
Where (t2.field24> = '')
And t1.fid = t4.fid );
---------------------------- Oracle --------------------------------------------
Update Table1 T1
Set (field1, field2) = (select field21, field22
From Table2 T2
Inner join table3 T3
On t3.field31 = t2.field23
Inner join table4 T4
On t3.field32 = t4.filed41
Where (t2.field24> = '')
And t1.fid = t4.fid ))
Where exists (select field21, field22
From Table2 T2
Inner join table3 T3
On t3.field31 = t2.field23
Inner join table4 T4
On t3.field32 = t4.filed41
Where (t2.field24> = '')
And t1.fid = t4.fid ));
--------------------------------- DB2 ------------------------------------------
Update Table1 as T1
Set (field1, field2) = (select field21, field22
From Table2 T2
Inner join table3 T3
On t3.field31 = t2.field23
Inner join table4 T4
On t3.field32 = t4.filed41
Where (t2.field24> = '')
And t1.fid = t4.fid ))
Where exists (select field21, field22
From Table2 T2
Inner join table3 T3
On t3.field31 = t2.field23
Inner join table4 T4
On t3.field32 = t4.filed41
Where (t2.field24> = '')
And t1.fid = t4.fid ));