通过子查询进行UPDATE

目的：更新父表的字段值，且要更新其子表的相关字段值（备注：没有在子表上建外键）。

方法：先建个临时表（和父表的数据一模一样，也可以看成是父表的备份表），然后根据子查询进行UPDATE。

示例：

父表：test.dbo.dept

子表：test.dbo.emp

父表，子表查询结果：

步骤：

1：查看哪些子表用到了test.dbo.dept的deptno

可以通过这个存储过程：

--存储过程创建语句（这是我同事写的）

USE [DBA]

GO

/****** Object:  StoredProcedure [JCW].[Find_Column_Data]    Script Date: 2014/2/19 14:04:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

查找全服务器中使用到此列名的表及是否有表值的信息

20130716

江成威

*/

CREATE PROC [JCW].[Find_Column_Data]

(

@ColumnName nvarchar(200),

@Data nvarchar(max)=NULL

)

AS

begin

CREATE TABLE #NCTables

(

Num INT IDENTITY(1,1),

TableFullName NVARCHAR(500),

HasColumnData BIT DEFAULT 0

)

DECLARE @SS nvarchar(max)='

use ?;

INSERT INTO #NCTables

        ( TableFullName, HasColumnData )

SELECT ''[?].[''+S.name+''].[''+T.name+'']'',0 FROM sys.columns C INNER JOIN sys.tables T ON C.object_id=T.object_id INNER JOIN sys.schemas S ON S.schema_id=T.schema_id WHERE C.name='''+@ColumnName+''';

'

EXEC sys.sp_MSforeachdb @SS

--SELECT * FROM #NCTables

--DROP TABLE #NCTables

DECLARE @Num int =(SELECT MAX(Num) from #NCTables)

DECLARE @TBName NVARCHAR(500)

DECLARE @SQL NVARCHAR(max)

DECLARE @where NVARCHAR(max) = (CASE WHEN @Data IS NULL THEN '' ELSE ' where '+@ColumnName+'='''+@Data+'''' END)

WHILE @Num>0

BEGIN 

SELECT @TBName=TableFullName FROM #NCTables WHERE num=@Num

SET @SQL='if (select count(0) from '+@TBName+' '+@where+')>=1

begin 

update #NCTables set HasColumnData=1 where Num='''+cast(@Num AS nvarchar(20))+'''

end

'

PRINT @sql

EXEC (@sql)

SET @Num=@Num-1

END

SELECT * FROM #NCTables

END

GO

--执行存储过程

EXEC DBA.JCW.Find_Column_Data @ColumnName = N'deptno', -- nvarchar(200) 字段名称 
    @Data = N'' -- nvarchar(max) 字段值

--结果：

test.dbo.emp

--2 对这两个表进行备份：

USE dba;
SELECT *
INTO dept_bak_bdd_201402191726
FROM TEST.DBO.DEPT;


SELECT *
INTO emp_bak_bdd_201402191726
FROM TEST.DBO.EMP

--3 更新dept表

UPDATE dept
SET deptno=40
WHERE deptno=30;

--4 根据子查询更新emp

UPDATE TEST.DBO.EMP
SET deptno=c.deptno
--SELECT A.deptno,c.deptno,B.dNAME
FROM TEST.DBO.EMP A
INNER JOIN  DBA.dbo.dept_bak_bdd_201402191726 B ON B.deptno=A.deptno
INNER JOIN TEST.DBO.DEPT C ON B.dName=C.dNAME

--5 验证

SELECT *
FROM test.dbo.EMP;

所有deptno为30的现都已改成了40.