Multiple row delete syntax error

Hello

I am trying to delete multiple rows in my sql 2000 database, I have used the following sysntax but I keep getting errors:

DELETE From NameList

WHERE LName = 'Smith';
and
LName = 'Jones';
and
LName = 'Peters';
and
LName = 'Adams';
and
LName = 'Conner';
and
LName = 'Simon';

I have tried editing the syntax in a variety of ways, but I just can't find the correct solution.

I have Googled and so far not found another syntax format. What am I doing wrong.

Thanks.

Lynn

First, a semi-colon ( ; ) tells SQL Server that is the end of the statement. So you are probably getting incorrect syntax on line 3 above. I think what you want to do is this:

DELETE From NameList
WHERE LName = 'Smith'
OR
LName = 'Jones'
OR
LName = 'Peters'
OR
LName = 'Adams'
OR
LName = 'Conner'
OR
LName = 'Simon';

|||

Try this to work faster

DELETE From NameList
WHERE LName in( 'Smith','Jones','Peters','Adams','Conner','Simon')

and is much shorted and easy to create in .Net

Thanks

|||

Hi Guys

Thanks a lot your syntax works great and so fast too.

You have saved me hours of manually deleting rows. As I also have have many other tables needing rows deleted.

Cheers

Lynn

|||

Hello jpazgier

By the way, before I try this out and perhaps create a problem.

Does your shortened version work to inset data into multiple rows also?

This is the way I have inserted in the past, row by row.:

INSERT INTO LName ( [LName])
VALUES( N'Murphy')

I need to insert multiple rows, will this do the job?

INSERT Into NameList ( [LName])
VALUES (N' 'Smith','Jones','Peters','Adams','Conner','Simon')

Thanks

Lynn

|||

no it will not allow you to insert multiple rows this way, but for delete it will work perfectly

If you would like to use comma delimited values to insert you can create table returned function which will accept your string and generate table for you and next you can use

INSERT Into NameList ( [LName])
SELECT NAME
from dbo.SplitNamesByComma('Smith,Jones,Peters,Adams,Conner,Simon')

You can also loop throw string from comma to comma and insert names one by one inside stored procedure.

Thanks

|||

Hello jpazgier

Thanks for the informaton.

I thought I had found a quick solution to multiple row inserts, I was feeling quite pleased with myself.

Thanks

|||

maybe you can try this for multiple inserts?

create

table #aa(aavarchar(10))
insertinto #aa
SELECT'aa'
UNION
SELECT'bb'
UNION
SELECT'cc'

select*from #aa

So you only have to replace comma by UNION SELECT maybe it will work for you. It will save you some amount of time because it will be single insert.

Thanks