Solved

Conditional delete from query in sql enterprise manager

Posted on 2006-11-27
5
298 Views
Last Modified: 2013-12-03
Trying to delete rows for the condition below in sql enterprise manager does nothing but tell me the query was run.

delete from downloads
where id < 5000
and filesize < 200000

I want to delete all rows where the id is less than 5000 and the filesize is less than 200000. There are no other conditions. No triggers I can see. Nothing unusual. If I remove the conitions and simply run "delete from downloads" the rows are deleted, except I do get a timeout.

So, what might I be doing wrong and how can I correct it?

 - Georgia

0
Comment
Question by:RollinNow
[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
5 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 18017831
Did you try it from Query analyser ? the problem with the EM is that it will create a duplicate of that table and try to delete the data from there,  so this is not advisable.
0
 

Author Comment

by:RollinNow
ID: 18017844
Yes I did, that is, just now, first time. I was told there were 0 rows affected. I'm sure the id and filesize values are correct.

Also, is there a timeout statement I can use in the query?
0
 
LVL 9

Accepted Solution

by:
dduser earned 300 total points
ID: 18017860
Select * from downloads
where id < 5000
and filesize < 200000

How many rows this returns???

Regards,

dduser
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 18017889
>I want to delete all rows where the id is less than 5000 and the filesize is less than 200000.
do you mean to delete all the rows that have id 5000    and delete all the rows  that have filesize less than 200000, or do you need both conditions at the same time?
if you need either condition, then use OR:
delete from downloads
where ( id < 5000 or filesize < 200000 )

resp:
delete from downloads
where id < 5000

delete from downloads
where filesize < 200000


0
 

Author Comment

by:RollinNow
ID: 18017988
Looks like the rows were all above 200000 filesize. Silly me!

Thanks for holding my hand!

 - Georgia
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

749 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