?
Solved

rowcount delete

Posted on 2003-03-05
7
Medium Priority
?
2,110 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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: 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.

Question has a verified solution.

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

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

765 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