Methods for updating data with associated subquery UPDATE statements _ database other

Update is a simpler statement in T-SQL, and the Update table set column=expression [WHERE condition], we all use it. But the use of update is not only this, really in the development of the time, flexibly and appropriately using the update can achieve a multiplier effect.

Assuming that there are table Table1 (a,b,c) and Table2 (A,c), some record field C in Table1 is NULL, and you want to update Table2 by looking in the field a in Table1, and by taking the value of field C, which is equal to field a. A conventional way of thinking, through a cursor traversing all records in TABLE1 field C null, looking for Table2 in the loop body and updating it in the form of a cursor cursor. The test SQL statement is as follows:

--1. Create a Test table Table1 (a varchar), B varchar (a), C varchar (a), CONSTRAINT [Pk_table1] P Rimary KEY CLUSTERED (a ASC)) on [PRIMARY] Create TABLE Table2 (a varchar (a), C varchar ( CONSTRAINT [Pk_table2] PRIMARY KEY CLUSTERED (a ASC)) on [PRIMARY] go--2. Create test Data Insert into Table1 values (' Zhao ', ' ASDs ', null) insert INTO TABLE1 values (' money ', ' ASDs ', ', ') insert into Table1 values (' Sun ', ' ASDs ', ') INSERT into Table1 values (' Lee ', ' ASDs ', null) insert INTO Table2 values (' Zhao ', ', ') insert into Table2 values (' Money ', ' INSERT into Table2 values (' Sun ', ' Table2 ') insert into the values (' Lee ', ' ') ' Go select * Table1--3. Through the cursor side  Update DECLARE @name varchar DECLARE @score varchar (a) Declare mycursor for select a from cursor where C NULL open MyCursor fetch NEXT from MyCursor to @name while (@ @fetch_status = 0) BEGIN Select @score =c from T
    Able2 where a= @nameUpdate Table1 Set c = @score where a = @name fetch next from MyCursor to @name end close MyCursor deallocate MyCursor go--4. Show updated Results select * from Table1 go--5. Delete Test table drop table Table1 drop table Table2

Although the cursor can be implemented, but the code looks very complex, in fact, update based on the child Association updates as long as a single statement can be done, the test code is as follows:

--1. Creating a Test Table CREATE TABLE Table1 (a varchar (a), B varchar (a), C varchar (1
    0), CONSTRAINT [Pk_table1] PRIMARY KEY CLUSTERED (a ASC)) on [PRIMARY] Create TABLE Table2 ( A varchar (a), C varchar (a), CONSTRAINT [Pk_table2] PRIMARY KEY CLUSTERED (a ASC)) on [PRIM  ARY] Go--2. Create test data insert into Table1 values (' Zhao ', ' ASDs ', null) insert INTO TABLE1 values (' money ', ' ASDs ', ') insert
  into Table1 values (' Sun ', ' ASDs ', ') insert into Table1 values (' Lee ', ' ASDs ', null) insert INTO Table2 values (' Zhao ', ' 90 ') INSERT into Table2 values (' money ', ') insert into Table2 values (' Sun ', ' s ') insert into Table2 values (' Lee ', '
  Elect * from Table1--3. Updates update Table1 Set c = (select C from Table2 where a = table1.a) where c is null Go--4. Show updated Results select * from Table1 go--5. Delete Test table drop table Table1 drop table Table2 

Resources: Perhaps an ignored UPDATE statement, update subquery