?
Solved

rowcount delete

Posted on 2003-03-05
7
Medium Priority
?
2,154 Views
Last Modified: 2008-03-03
I need a this delete query to stop at 100,000 rows. How do I do this, rowcount? Basically this is to prevent the log from choking. I am using Sybase ASE 12.0.

delete from logtable where username = 'testuser' and datediff(dd, commandDate, getdate()) > 30
0
Comment
Question by:s0lar1s8
7 Comments
 

Accepted Solution

by:
golgi_ap earned 90 total points
ID: 8072240
set rowcount 100000
go
delete from logtable where username = 'testuser' and datediff(dd, commandDate, getdate()) > 30
0
 

Author Comment

by:s0lar1s8
ID: 8072624
Sorry that does not work with a delete, it works only if I am  seleting rows to be returned.
0
 

Author Comment

by:s0lar1s8
ID: 8072765
Sorry that does not work with a delete, it works only if I am  seleting rows to be returned.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 4

Expert Comment

by:gletiecq
ID: 8073867
If you don't mind slowing the process down, you can delete from within a cursor and stop the cursor once it reaches the required number of rows.  A cursor is going to be a lot slower, and you might run into some concurrency issues.  Of course, if you ran the cursor with a smaller limit followed by a COMMIT TRAN and ran the cursor several times, that might help.

You may want to consider an index on commandDate if you use a cursor.  It might help a lot.

You might want to consider tuning the transaction log, though.  Either setting up a last chance threshold procedure to dump the transaction log, or increasing the size of the log might help.  

Hope this helps.

Greg
0
 
LVL 1

Expert Comment

by:walzkev
ID: 8114148
Another option would be to set rowcount 10000 and then do a select into a work table the key values from the table you wish to delete from.  Then just do a delete maintable from maintable, worktable where maintable.key = worktable.key
0
 

Expert Comment

by:EoinWoods
ID: 8134976
Actually, golgi_ap is correct.  You can use "rowcount" for this.

For example:

1> select count(1) from t1
2> go

 -----------
           5

(1 row affected)
1> set rowcount 2
2> go
1> delete from t1
2> go
(2 rows affected)
1> select count(1) from t1
2> go

 -----------
           3

(1 row affected)

0
 

Author Comment

by:s0lar1s8
ID: 8136277
Thanks. I have solved the problem.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The following information will get you familiar with your new DV server, including the (mt) Account Center, the Plesk Control Panel, our world-renowned support department and the rest of the (mt) tools that come with your new service.
This article explains how to use the rsync command to create backups and sync data across hosts. Rsync is a very useful command that is often used to copy data, make backups, migrate hosts, and bridge the gap between site staging and production envi…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question