Delete first 2 records

[Per9922]

Hello,

I need the help from you! I would like to delete the first two records. I have tried this:

DELETE FROM LoggingData WHERE (SELECT TOP 2 * FROM LoggingData ORDER BY DateAndTime)

Please help!

 

[Per9922]

Found the solution:

DELETE FROM LoggingData Where DateAndTime In (SELECT TOP(2) DateAndTime FROM LoggingData ORDER BY DateAndTime)

 

[Olaf Doschke]

WHERE conditions have to be boolean expressions, a query returns records. A boolean expression can be made if you specify records should be IN a lit of records, but you typically need an ID (primary key field) to identify what is IN the set of records or not, eg

DELETE FROM LoggingData WHERE LoggingData.ID IN (SELECT TOP 2 ID FROM LoggingData ORDER BY DateAndTime)

As the order is given by dateandtime it would be sufficient to do it this way, I think:

DELETE FROM LoggingData WHERE LoggingData.DateAndTime IN (SELECT TOP 2 DateAndTime FROM LoggingData ORDER BY DateAndTime)

Bye, Olaf.

 

[jbenson001]

You can also use a CTE

;WITH CTE AS
(
SELECT TOP 2 *
FROM [LoggingData ]
 ORDER BY DateAndTime
)
DELETE FROM CTE

 

[Olaf Doschke]

Nice, I tried it and can confirm.
What would it do in situations you do joins in the CTE?

I see, that'll give "Msg 4405...View or function 'CTE' is not updatable because the modification affects multiple base tables."
But with a single table query this works.

Bye, Olaf.

 

[Per9922]

Thanks Olof & jbenson001 for the respone! You are so helpful!! Thanks and have a nice weekend

 

[jbenson001]

@Per9922 you are welcome

@Olaf - In order to avoid that error you would have to do the join to the cte outside of the cte definition.