Syntax Help Please - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Syntax Help Please

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-07-24 : 06:34:59

Hello,

I have a view which has 4 tables in, basically to pull together additional bits of information alongside my main table (tbl_HolidayRequests).

Now I have this view, I'd like to delete the results from the main table (tbl_HolidayRequests).

However, because I'm linking other tables with this, it won't let me.

What would the syntax be please ?

My current SQL is :


SELECT     TOP 100 PERCENT dbo.tbl_HolidayRequests.HolidayID, dbo.tbl_HolidayRequests.Received, dbo.tbl_HolidayRequests.EmployeeNumber, 
                      dbo.tbl_EmployeeDetails.FullName, dbo.tbl_EmployeeDetails.HolidayGroup, dbo.tbl_Teams.TeamName, 
                      CAST(dbo.tbl_HolidayRequests.HolidayDate AS datetime) AS HolidayDate, dbo.tbl_HolidayRequests.AM, dbo.tbl_HolidayRequests.PM, 
                      dbo.tbl_HolidayRequests.NumberOfDays, dbo.tbl_HolidayRequests.NumberOfHours, dbo.tbl_HolidayRequests.OtherDetails, 
                      dbo.tbl_HolidayRequests.ROC, dbo.tbl_HolidayRequests.NoticeDate, dbo.tbl_HolidayRequests.OriginalRequestDates, 
                      dbo.tbl_HolidayGroups.ResourceCheck
FROM         dbo.tbl_HolidayRequests INNER JOIN
                      dbo.tbl_EmployeeDetails ON dbo.tbl_HolidayRequests.EmployeeNumber = dbo.tbl_EmployeeDetails.EmployeeNumber INNER JOIN
                      dbo.tbl_Teams ON dbo.tbl_EmployeeDetails.TeamID = dbo.tbl_Teams.TeamID INNER JOIN
                      dbo.tbl_HolidayGroups ON dbo.tbl_EmployeeDetails.HolidayGroup = dbo.tbl_HolidayGroups.HolidayGroup
WHERE     (dbo.tbl_HolidayGroups.ResourceCheck = 1) AND (CAST(dbo.tbl_HolidayRequests.HolidayDate AS datetime) >= CONVERT(DATETIME, 
                      '2008-12-24 00:00:00', 102) AND CAST(dbo.tbl_HolidayRequests.HolidayDate AS datetime) <= CONVERT(DATETIME, '2009-01-02 00:00:00', 102))
ORDER BY CAST(dbo.tbl_HolidayRequests.HolidayDate AS datetime), dbo.tbl_EmployeeDetails.HolidayGroup, dbo.tbl_Teams.TeamName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:46:05

This is your view, right?

CREATE VIEW	dbo.vwMyView
AS

SELECT		dbo.tbl_HolidayRequests.HolidayID,
		dbo.tbl_HolidayRequests.Received,
		dbo.tbl_HolidayRequests.EmployeeNumber,
		dbo.tbl_EmployeeDetails.FullName,
		dbo.tbl_EmployeeDetails.HolidayGroup,
		dbo.tbl_Teams.TeamName,
		CAST(dbo.tbl_HolidayRequests.HolidayDate AS DATETIME) AS HolidayDate,
		dbo.tbl_HolidayRequests.AM,
		dbo.tbl_HolidayRequests.PM,
		dbo.tbl_HolidayRequests.NumberOfDays,
		dbo.tbl_HolidayRequests.NumberOfHours,
		dbo.tbl_HolidayRequests.OtherDetails,
		dbo.tbl_HolidayRequests.ROC,
		dbo.tbl_HolidayRequests.NoticeDate,
		dbo.tbl_HolidayRequests.OriginalRequestDates,
		dbo.tbl_HolidayGroups.ResourceCheck
FROM		dbo.tbl_HolidayRequests
INNER JOIN	dbo.tbl_EmployeeDetails ON dbo.tbl_EmployeeDetails.EmployeeNumber = dbo.tbl_HolidayRequests.EmployeeNumber
INNER JOIN	dbo.tbl_Teams ON dbo.tbl_Teams.TeamID = dbo.tbl_EmployeeDetails.TeamID
INNER JOIN	dbo.tbl_HolidayGroups ON dbo.tbl_HolidayGroups.HolidayGroup = dbo.tbl_EmployeeDetails.HolidayGroup
WHERE		dbo.tbl_HolidayGroups.ResourceCheck = 1
		AND CAST(dbo.tbl_HolidayRequests.HolidayDate AS DATETIME) >= '20081224'
		AND CAST(dbo.tbl_HolidayRequests.HolidayDate AS DATETIME) < '20090102'

And you want to delete records from tbl-HolidayRequests?

Make a DELETE query and join the view!

DELETE		x
FROM		dbo.tbl_HolidayRequests AS x
INNER JOIN	dbo.vwMyView AS w ON w.HolidayID = x.HolidayID

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-07-24 : 06:51:16

Yes I want to delete the records that appear in the view from tbl_HolidayRequests.

I'll give your code a try.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources