Why isn't this delete statement working? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Why isn't this delete statement working?

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-25 : 14:30:27

delete
from carnivalshipnames t,
(select max(executiondatetime) as executiondatetime
from carnivalshipnames) r
where not t.executiondatetime = r.executiondatetime or t.executiondatetime is null

is not working. It gives this error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 't'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'r'.

but!

select *
from carnivalshipnames t,
(select max(executiondatetime) as executiondatetime
from carnivalshipnames) r
where not t.executiondatetime = r.executiondatetime or t.executiondatetime is null

IS working.... I don't understand why one wolud work but not the other

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-25 : 14:35:26

The point of this delete statement is to keep only the newest set of data in the table.

it finds the newest date in column executiondatetime via max(executiondatetime), then it should delete every entry that does not have a date of max(executiondatetime)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-25 : 14:55:22

It isn't working because it's invalid syntax

delete t 
from
	carnivalshipnames t
...etc.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources