/*
Using update updates with associated subqueries
--1. Create a test table
Create TABLE Table1
(
A varchar (10),
b varchar (10),
C varchar (10),
CONSTRAINT [Pk_table1] PRIMARY KEY CLUSTERED
(
A ASC
)
) on [PRIMARY]
Create TABLE Table2
(
A varchar (10),
C varchar (10),
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 ', ' 100 ')
Insert into Table1 values (' Sun ', ' ASDs ', ' 80 ')
Insert into Table1 values (' Lee ', ' ASDs ', null)
Insert into Table2 values (' Zhao ', ' 90 ')
Insert into Table2 values (' money ', ' 100 ')
Insert into Table2 values (' Sun ', ' 80 ')
Insert into Table2 values (' Lee ', ' 95 ')
Go
SELECT * FROM Table1
--3. Update via Update method
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