how to delete a specific record inside a DB?

In one of our DB's, we've got a corrupted record. My question to this group,
is there a command to delete a specific record out of the DB? If so, what
all information do i have to provide to the command, so it specifies this
record. Any help is greatly appreciated
Ken Zimmerman
MIS Dept
American Red Cross
The DELETE statement deletes a row identified by its column values. If you
aren't sure what the key columns of the table are then before you delete
anything you'll want to be sure you've found the right row(s). Check by
using a SELECT statement to view the data you are going to delete. For
example:
SELECT *
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
Once you're happy that you've defined the correct criteria for the row(s)
you want to delete:
DELETE
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
May be wise also to make sure you have a recent backup before deleting
anything.
David Portas
SQL Server MVP
|||KZimmerman wrote:
> In one of our DB's, we've got a corrupted record. My question to
> this group, is there a command to delete a specific record out of the
> DB? If so, what all information do i have to provide to the command,
> so it specifies this record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross
I assume by corrupted, you mean that there is strange data in one of the
columns and you believe this to be a "bad" data issue, not a corruption
in the database.
If so, use David's recommendation and locate the primary key for the row
or rows with the problem so you can issue a delete statement to remove
them from the database.
If this is a database corruption issue, try issuing DBC CHECKDB on the
database to check for problems.
David G.
|||If none of that works. you might back up your database and try.
Dbcc checkdb repair_allow_data_loss (read about this in books on line first)
or
export the rows out using bcp ( you'll get the rows you can see)
truncate the table and re-import them (Be careful to get all of the rows ,
you might have to do some tricks to select forward (and it dies when you get
to the bad row, then select backwards to get the rows on the other side.)
Also,
Call MS Tech support, I think they have some tools which might be useful I
think their fee is $250... Not much considering the time you might spend
messing with this ( if the data is important.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"KZimmerman" <KZimmerman@.discussions.microsoft.com> wrote in message
news:1087034D-3D76-4827-9166-EA3AB7D6CE91@.microsoft.com...
> In one of our DB's, we've got a corrupted record. My question to this
group,
> is there a command to delete a specific record out of the DB? If so, what
> all information do i have to provide to the command, so it specifies this
> record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross