Query in sql server?

Question

how to replace this query in sql server ?

DELETE FROM student 
WHERE id=2 
 AND list_column_name='lastName' 
  OR list_column_name='firstName' 
LIMIT 3;

You are trying to delete no more than 3 records, that match the WHERE clause? — David Rushton
– David Rushton, Commented Dec 18, 2015 at 11:55

Martin Smith · Accepted Answer · 2015-12-18 12:01:49Z

2

There is no ORDER BY in your original query.

If you just want to delete an arbitary three records matching your WHERE you can use.

DELETE TOP(3) FROM student
WHERE  id = 2
       AND list_column_name = 'lastName'
        OR list_column_name = 'firstName';

For greater control of TOP (as ordered by what?) you need to use a CTE or similar.

answered Dec 18, 2015 at 12:01

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mohan111 · Accepted Answer · 2015-12-18 12:32:28Z

1

using CTE and TOP 3 where equal to LIMIT 3

;WITH CTE
     AS (SELECT TOP 3 Studentname
         FROM   student
         WHERE  id = 2
                AND list_column_name = 'lastName'
                 OR list_column_name = 'firstName')
DELETE FROM CTE

edited Dec 18, 2015 at 12:32

answered Dec 18, 2015 at 11:57

mohan111

8,8854 gold badges35 silver badges61 bronze badges

3 Comments

Rahul Over a year ago

CTE its not excepting, does CTE means column name

mohan111 Over a year ago

can you tell me in which environment you are executing ?

Martin Smith Over a year ago

You were missing a column list between TOP 3 and FROM

Collectives™ on Stack Overflow

Query in sql server?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related