TSQL--关联更新和删除

--===================================================

--测试CODE

USE tempdb;

GO

DROP TABLE TB1

GO

DROP TABLE TB2

GO

SELECT TOP(10) 

ROW_NUMBER()OVER(ORDER BY object_id) AS RID,

object_id AS ObjectID 

INTO TB1 

FROM sys.all_objects

GO

SELECT TOP(10) 

ROW_NUMBER()OVER(ORDER BY object_id DESC) AS RID, 

object_id AS ObjectID 

INTO TB2 

FROM sys.all_objects

--=================================================== 

--更新前查看数据

 

SELECT * FROM TB1

SELECT * FROM TB2

GO

--===================================================

--关联更新数据 5 条数据

UPDATE TOP(5) TB1

SET ObjectID=T2.ObjectID

FROM TB1 AS T1 INNER JOIN TB2 AS T2

ON T1.RID=T2.RID

 

--===================================================

--更新后查看数据

SELECT * FROM TB1

SELECT * FROM TB2

 

--===================================================

--关联删除数据 5 条数据

DELETE TOP(5) TB1

FROM  TB1 AS T1 

INNER JOIN TB2 AS T2

ON T1.RID=T2.RID

WHERE T1.ObjectID<>T2.ObjectID

 

--===================================================

--删除后查看数据

SELECT * FROM TB1

SELECT * FROM TB2