How to write a query to delete selected Rows?

select distinct * into #a from tbl

begin tran
delete tbl
insert tbl select * from #a
commit tran

put in your own error processing


or

set rowcount 1
while 1 > (select count(*) from tbl)
   delete tbl
set rowcount 0

or
set rowcount 3
delete tbl
set rowcount 0

or
declare @sql varchar(1000), @cnt int
select @cnt = count(*) - 1 from tbl

select @sql = 'set rowcount ' + convert(varchar(10),@cnt) + ' delete tbl'

exec (@sql)

set rowcount 1
while 1 > (select count(*) from tbl)
  delete tbl
set rowcount 0


s.b.

set rowcount 1
while 1 < (select count(*) from tbl)
  delete tbl
set rowcount 0

Hi Soft_ware,

There is one problem with this approach.

You do not have any primary key.

SQL Server cannot handle duplicate records in a table structure.

So you will first need to change the DDL of the table structure.
[Add an identity column - int [Autonumber]]
   
Pri.Key  Name       Age     Sex
 1      ABC        12       M
 2      ABC        12       M
 3      ABC        12       M
 4      ABC        12       M
Then issue your delete statement: delete from <table> where Pri.Key < (select max(Pri.Key) from <table>)

This will always leave the last record in the table.

It would be a good idea to have tables with Primary Keys - [Or a completly unique identifier for every record] otherwise the database will not function correctly.

One other technique : Issue a Unique Constraint on the Name column.

Your delete statement would be:
delete from <table> where Name < (select max(Name) from <table>) this would leave the last record in the table available for use.

Regards,

Dan.

Check this FAQ question:
"How can I delete duplicate rows?"
http://www.MSSQLCity.com/FAQ/Devel/DelDupRows.htm


With best regards,
Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

Dear soft_ware,

These are your outstanding Q's:
https://www.experts-exchange.com/questions/20319017/Is-it-possible-to-avoid-the-special-meaning-on-'-'-backslash-in-a-MySql-Query.html
https://www.experts-exchange.com/questions/20310655/Getting-a-list-of-computers-connected-to-a-network.html
https://www.experts-exchange.com/questions/20310709/Getting-a-List-of-Shared-Folders-In-computer-connected-to-a-network.html
https://www.experts-exchange.com/questions/20440256/SENDING-A-FAX-USING-VB.html
https://www.experts-exchange.com/questions/20283355/How-to-write-a-query-to-delete-selected-Rows.html
None of them is closed and many lack response comments from you.

Please finalize them !

Expert(s),

Please post a closure recommandation.
When no comment has been added within seven days I'll finalize this Q with a:

- PAQ and no refund

Thanks !

modulo

Community Support Moderator
Experts Exchange