CODEWITHSUNDEEP
sql-server
user974015
user974015

Reputation: 113

SQL query to delete top item

situation im trying to run this query, i want to delete the the most recent data i've saved in the a table.

DELETE from JOBORDER2 Where ddates in (SELECT TOP 1 * FROM JOBORDER2 Where cjobno = '45' ORDER BY ddates DESC);

but i got this error. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 0

Views: 260

Answers (2)

John Saunders
John Saunders

Reputation: 161783

Try

DELETE from JOBORDER2
Where ddates in (
    SELECT TOP 1 ddates
    FROM JOBORDER2
    Where cjobno = '45'
    ORDER BY ddates DESC);

The idea is that you're trying to return a set of (1) "ddates".

Explanation

Let's assume that the top "ddates" was '11/30/2012'. Then your query would be equivalent to:

DELETE from JOBORDER2
Where ddates in ('11/30/2012');

which makes sense. With "*", you were trying to do something nonsensical. There was a rowset in parentheses, and even if there were only one row, you were dealing with more than one column. Which column did you want to compare to?

Upvotes: 4

JBrooks
JBrooks

Reputation: 10013

It doesn't like the star. Try:

DELETE from JOBORDER2 Where ddates in
      (SELECT TOP 1 ddates 
       FROM JOBORDER2 
       Where cjobno = '45' 
       ORDER BY ddates DESC);

Upvotes: 3

Related Questions