Thursday, October 28, 2010

SQL SERVER : UPDATING MULTIPLE FIELDS FROM ANOTHER TABLE IN A SINGLE QUERY

Two syntaxes are possible :


1.



===================================================================


UPDATE
DailyAttendanceTable SET DailyAttendanceTable.Class = B.Class ,DailyAttendanceTable.[Division] = B.[Division],DailyAttendanceTable.[RouteNo] = B.[RouteNo],DailyAttendanceTable.[PickUpPoint] = B.[PickUpPoint],DailyAttendanceTable.[DropRouteNo] = B.[DropRouteNo],DailyAttendanceTable



 UPDATE ASET A.Class = B.Class ,A.[Division] = B.[Division],A.[RouteNo] = B.[RouteNo],A.[PickUpPoint] = B.[PickUpPoint],A.[DropRouteNo] = B.[DropRouteNo],A

.[DropDownPoint] = B.[DropDownPoint]FROM DailyAttendanceTable A JOIN SCHULE_STUDENTDETAILS BON A.StudentId = B.StudentId



.[DropDownPoint] = B.[DropDownPoint]FROM SCHULE_STUDENTDETAILS BWHERE DailyAttendanceTable.StudentId = B.StudentId
UPDATE DailyAttendanceTable SET DailyAttendanceTable.Class = SCHULE_STUDENTDETAILS.Class ,DailyAttendanceTable.[Division] = SCHULE_STUDENTDETAILS.[Division],DailyAttendanceTable.[RouteNo] = SCHULE_STUDENTDETAILS.[RouteNo],DailyAttendanceTable.[PickUpPoint] = SCHULE_STUDENTDETAILS.[PickUpPoint],DailyAttendanceTable.[DropRouteNo] = SCHULE_STUDENTDETAILS.[DropRouteNo],DailyAttendanceTable







2.UPDATE DailyAttendanceTable SET DailyAttendanceTable.Class = SCHULE_STUDENTDETAILS.Class ,DailyAttendanceTable.[Division] = SCHULE_STUDENTDETAILS.[Division],DailyAttendanceTable.[RouteNo] = SCHULE_STUDENTDETAILS.[RouteNo],DailyAttendanceTable.[PickUpPoint] = SCHULE_STUDENTDETAILS.[PickUpPoint],DailyAttendanceTable.[DropRouteNo] = SCHULE_STUDENTDETAILS.[DropRouteNo],DailyAttendanceTable






Aliasing can be done  like this :
1 A.

UPDATE DailyAttendanceTable SET DailyAttendanceTable.Class = B.Class ,DailyAttendanceTable.[Division] = B.[Division],DailyAttendanceTable.[RouteNo] = B.[RouteNo],DailyAttendanceTable.[PickUpPoint] = B.[PickUpPoint],DailyAttendanceTable.[DropRouteNo] = B.[DropRouteNo],DailyAttendanceTable





2 A.
UPDATE ASET A.Class = B.Class ,A.[Division] = B.[Division],A.[RouteNo] = B.[RouteNo],A.[PickUpPoint] = B.[PickUpPoint],A.[DropRouteNo] = B.[DropRouteNo],A



.[DropDownPoint] = B.[DropDownPoint]FROM DailyAttendanceTable A JOIN SCHULE_STUDENTDETAILS BON A.StudentId = B.StudentId
.[DropDownPoint] = B.[DropDownPoint]FROM SCHULE_STUDENTDETAILS BWHERE DailyAttendanceTable.StudentId = B.StudentId
.[DropDownPoint] = SCHULE_STUDENTDETAILS.[DropDownPoint]FROM DailyAttendanceTable JOIN SCHULE_STUDENTDETAILS ON DailyAttendanceTable.StudentId = SCHULE_STUDENTDETAILS.StudentId


.[DropDownPoint] = SCHULE_STUDENTDETAILS.[DropDownPoint]FROM SCHULE_STUDENTDETAILS WHERE DailyAttendanceTable.StudentId = SCHULE_STUDENTDETAILS.StudentId

No comments:

Post a Comment