Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
22858 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  
 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