SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 To update several tables in one execution
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svgeorge
Starting Member

USA
23 Posts

Posted - 09/26/2007 :  09:58:56  Show Profile  Reply with Quote
How do I update several tables at one single execution

below is my each update command and I saved this as sql file and if I open this file and exucute will it update all tables or syntax needs to be changed please suggest.

UPDATE dbo.Payment_Enrollment_AIMS
SET dbo.Payment_Enrollment_AIMS.[PROJ END DT]= dbo.View_1.DTE_PROJ_END
FROM dbo.Payment_Enrollment_AIMS JOIN dbo.View_1
ON dbo.Payment_Enrollment_AIMS.Enrollment_ID=dbo.View_1.Enrollment_ID
where dbo.Payment_Enrollment_AIMS.JC_ID = dbo.View_1.JC_ID
and dbo.Payment_Enrollment_AIMS.[PROJ START DT] =dbo.View_1.[PROJ START DT]
and dbo.Payment_Enrollment_AIMS.[Grant]=dbo.View_1.[Grant]
and dbo.Payment_Enrollment_AIMS.TERM= dbo.View_1.TERM

GO

UPDATE dbo.Payment_Placement_AIMS
SET dbo.Payment_Placement_AIMS.[EMP END DT]= dbo.View_2.DTE_END_EMPLR
FROM dbo.Payment_Placement_AIMS JOIN dbo.View_2
ON dbo.Payment_Placement_AIMS.JC_ID = dbo.View_2.JC_ID
where dbo.Payment_Placement_AIMS.[EMP START DT] =dbo.View_2.[EMP START DT]
and dbo.Payment_Placement_AIMS.[Grant]=dbo.View_2.CDE_PROJ
and dbo.Payment_Placement_AIMS.HOURS= dbo.View_2.HOURS
and dbo.Payment_Placement_AIMS.WAGE=dbo.View_2.WAGE

GO

UPDATE dbo.Payment_Activity_AIMS
SET dbo.Payment_Activity_AIMS.[ACTV END DT]= dbo.View_3.DTE_END_ACTV
FROM dbo.Payment_Activity_AIMS JOIN dbo.View_3
ON dbo.Payment_Activity_AIMS.ACTIVITY_ID =dbo.View_3.ACTIVITY_ID
where dbo.Payment_Activity_AIMS.JC_ID = dbo.View_3.JC_ID
and dbo.Payment_Activity_AIMS.[ACTV START DT] = dbo.View_3.[ACTV START DT]
and dbo.Payment_Activity_AIMS.[ACTIVITY CODE]= dbo.View_3.[ACTIVITY CODE]
and dbo.Payment_Activity_AIMS.PROGRAM= dbo.View_3.PROGRAM

GO

UPDATE dbo.Payment_Activity_AIMS
SET dbo.Payment_Activity_AIMS.[ACTV END DT]= dbo.View_3.DTE_END_ACTV
FROM dbo.Payment_Activity_AIMS JOIN dbo.View_3
ON dbo.Payment_Activity_AIMS.ACTIVITY_ID =dbo.View_3.ACTIVITY_ID
where dbo.Payment_Activity_AIMS.JC_ID = dbo.View_3.JC_ID
and dbo.Payment_Activity_AIMS.[ACTV START DT] = dbo.View_3.[ACTV START DT]
and dbo.Payment_Activity_AIMS.[ACTIVITY CODE]= dbo.View_3.[ACTIVITY CODE]
and dbo.Payment_Activity_AIMS.PROGRAM= dbo.View_3.PROGRAM

GO

UPDATE dbo.Payment_Subproject_Retention_AIMS
SET dbo.Payment_Subproject_Retention_AIMS.[SUBPROJ END DT]= dbo.View_4.DTE_END_PROJ_SUB,
dbo.Payment_Subproject_Retention_AIMS.PROJ_END_DATE= dbo.View_4.DTE_PROJ_END
FROM dbo.Payment_Subproject_Retention_AIMS JOIN dbo.View_4
ON dbo.Payment_Subproject_Retention_AIMS.Subproject_Retention_ID =dbo.View_4.Subproject_Retention_ID
where dbo.Payment_Subproject_Retention_AIMS.JC_ID = dbo.View_4.JC_ID
and dbo.Payment_Subproject_Retention_AIMS.[SUBPROJ START DT] = dbo.View_4.[SUBPROJ START DT]
and dbo.Payment_Subproject_Retention_AIMS.[SUB PROJECT]= dbo.View_4.[SUB PROJECT]
and dbo.Payment_Subproject_Retention_AIMS.PROJ_START_DATE= dbo.View_4.DTE_PROJ_BEGIN

GO

UPDATE dbo.Payment_Placement_Retention_AIMS
SET dbo.Payment_Placement_Retention_AIMS.[EMP END DT]= dbo.View_5.DTE_END_EMPLR
FROM dbo.Payment_Placement_Retention_AIMS JOIN dbo.View_5
ON dbo.Payment_Placement_Retention_AIMS.Placement_Retention_ID =dbo.View_5.Placement_Retention_ID
where dbo.Payment_Placement_Retention_AIMS.JC_ID = dbo.View_5.JC_ID
and dbo.Payment_Placement_Retention_AIMS.[Grant] = dbo.View_5.[Grant]
and dbo.Payment_Placement_Retention_AIMS.[EMP START DT]= dbo.View_5.[EMP START DT]

GO

UPDATE dbo.Payment_Placement_Retention_AIMS
SET dbo.Payment_Placement_Retention_AIMS.PROJ_END_DATE = dbo.View_6.DTE_PROJ_END
FROM dbo.Payment_Placement_Retention_AIMS JOIN dbo.View_6
ON dbo.Payment_Placement_Retention_AIMS.Placement_Retention_ID =dbo.View_6.Placement_Retention_ID
where dbo.Payment_Placement_Retention_AIMS.JC_ID = dbo.View_6.JC_ID
and dbo.Payment_Placement_Retention_AIMS.[Grant] = dbo.View_6.[Grant]
and dbo.Payment_Placement_Retention_AIMS.PROJ_START_DATE= dbo.View_6.PROJ_START_DATE
and dbo.Payment_Placement_Retention_AIMS.CDE_PROJ_TERM= dbo.View_6.CDE_PROJ_TERM

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/26/2007 :  10:27:46  Show Profile  Reply with Quote
Yes it will update several tables, but you probably should surround the statements by a

BEGIN TRANSACTION

and then

COMMIT (if no errors)
or
ROLLBACK (if errors)

and take out the GO's between each statement (because they may prevent a rollback if there is an earlier error)

Kristen
Go to Top of Page

svgeorge
Starting Member

USA
23 Posts

Posted - 09/26/2007 :  13:21:07  Show Profile  Reply with Quote
Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000