Update associates with other tables to update data in batches

Source: Internet
Author: User

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 ));

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.