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
Solved

A problem occurred attempting to delete row sql

Posted on 2014-04-07
10
1,938 Views
Last Modified: 2014-04-07
I have this error when we tried to attempt to delete rows from a SQL databse table. The problem is bad people entered to register and that rows are messages the problem is there are around 200,000 trash messages we need to delete but we can´t with SQL Management Studio GUI. HEre is the map of the SQL database:

Man database called: "foro" a table called "dbo.messages". The problem is we have mixed the trash rows with good rows and we only know the GUI of SQL to select the rows to delete and that´s all but the error message appear. Now like you see in 200,000 trash rows each one by one is a nightmare.
   Anyone know how from the GUI selecting the rows to delete use that information maybe to delete in other way maybe in command of SQL? And what would be exact the command to delete that rows?
We have Windows 2008, SQL Server 2008.
Thank you
0
Comment
Question by:coerrace
  • 6
  • 4
10 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 39984180
And what's the difference between selecting rows one by one for deletion in GUI and deleting them one by one directly?

To post the error message would also help...

You have to find a pattern for deletion and use that pattern in WHERE clause of the DELETE command:

USE foro
DELETE FROM dbo.messages WHERE SomeConditionDerivedFromThePattern

The crerate the pattern shoud be relatively easy if you answer the question "How do I recognise records for deletion in GUI ?"

Of course, you have to create a backup before above commands...

Another way is to restore the database to the previous state where trash rows are not mixed with good rows yet.
0
 

Author Comment

by:coerrace
ID: 39984187
What could be the command exact to copy just the first 111 rows from one table to other table in the same database? I think we can solve like that because there is no pattern.
Thank you
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39984215
You may try

SELECT TOP 111 * INTO NewTable FROM ExistingTable

BUT not all sql engines allow TOP without ORDER BY clause so you will probably need something more:

SELECT TOP 111 * INTO NewTable FROM ExistingTable ORDER BY SomeExistingColumn

BUT now the "SomeExistingColumn" must ensure the rows for deletion will be at the top... Do you have some Timestamp in data?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 42

Expert Comment

by:pcelba
ID: 39984227
Another solution is to add one more column to your table and mark the first 111 records in this column. Then you may simply use this new column for data extraction, deletion etc. After the cleanup will be done you remove this column.
0
 

Author Comment

by:coerrace
ID: 39984228
This command
SELECT TOP 111 * INTO messages FROM messages_bad

Open in new window

messages_bad is the source where are the trash rows.

Say:

Msg 2714, Level 16, State 6, Line 1
There is already an object named 'messages' in the database.

Thank you
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 39984240
Yes dbo.messages table is in the database probably. You have to save the result into another table:

SELECT TOP 111 * INTO messages_new FROM messages_bad
0
 

Author Closing Comment

by:coerrace
ID: 39984276
Worked Excellent. Now in the new table is all the 11 rows and the 200,000 trash has gone.Thank you
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39984292
Please remember TOP 111 does not necessarily mean the first 111 rows from the table because the physical row order is not guaranteed in SQL standard and you should always include ORDER BY clause.
0
 

Author Comment

by:coerrace
ID: 39984300
Yes I know but copied all in right and perfect position each row. I just needed to add the set primary key and that´s all.
Thank you
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39984307
You are welcome!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

861 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