Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to update data with related subquery Update statements". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "how to achieve related subquery Update statements to update data" bar!

Update is the simplest statement in T-sql. We all use update table set column=expression [where condition]. But the usage of update is more than that. In real development, the flexible and appropriate use of update can achieve twice the result with half the effort.

Assuming that there are tables Table1 (a _ r _ b _ c) and Table2 (a _ r _ c), some record fields c in Table1 are now null, so you should look up the field an in Table2 according to field an and update Table1 by taking out the value of field c that is equal to field A. A conventional way of thinking is to use cursors to traverse all records in Table1 where field c is null, find Table2 in the loop and update it, that is, in the form of cursor Cursor. The test sql statement is as follows:

-1. Create a test table create TABLE Table1 (a varchar (10), b varchar (10), c varchar (10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED (an ASC)) ON [PRIMARY] create TABLE Table2 (a varchar (10), c varchar (10), CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED (an ASC)) ON [PRIMARY] GO-2. Create test data Insert into Table1 values ('Zhao', 'asds',null) Insert into Table1 values (' Qian', 'asds','100') Insert into Table1 values (' Sun', 'asds','80') Insert into Table1 values (' Li', 'asds',null) Insert into Table2 values (' Zhao', '90') Insert into Table2 values (' Qian', '100') Insert into Table2 values (' Sun', '80') Insert into Table2 values (' Li') ) GO select * from Table1-- 3. Update declare @ name varchar (10) declare @ score varchar (10) declare mycursor cursor for select a from Table1 where c is null open mycursor fetch next from mycursor into @ name while (@ @ fetch_status = 0) BEGIN select @ score=c from Table2 where a=@name update Table1 set c = @ score where a=@name fetch next from mycursor into @ name END close mycursor deallocate mycursor GO-- 4. Display the updated result select * from Table1 GO-- 5. Delete test table drop TABLE Table1 drop TABLE Table2

Although it can be implemented with cursors, the code looks very complex. In fact, it can be updated with only one statement based on sub-association with Update. The test code is as follows:

-1. Create a test table create TABLE Table1 (a varchar (10), b varchar (10), c varchar (10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED (an ASC)) ON [PRIMARY] create TABLE Table2 (a varchar (10), c varchar (10), CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED (an ASC)) ON [PRIMARY] GO-2. Create test data Insert into Table1 values ('Zhao', 'asds',null) Insert into Table1 values (' Qian', 'asds','100') Insert into Table1 values (' Sun', 'asds','80') Insert into Table1 values (' Li', 'asds',null) Insert into Table2 values (' Zhao', '90') Insert into Table2 values (' Qian', '100') Insert into Table2 values (' Sun', '80') Insert into Table2 values (' Li') ) GO select * from Table1-- 3. Update Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null GO-- 4. Display the updated result select * from Table1 GO-- 5. Delete the test table drop TABLE Table1 drop TABLE Table2 so far, I believe you have a deeper understanding of "how to update data with related subquery Update statements". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.