please help me not to DELETE the wrong rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

please help me not to DELETE the wrong rows

Author

Topic

littlejessihaha
Starting Member

5 Posts

Posted - 2004-11-09 : 09:24:07

Hi,

I have a table in my database called dbo_arch_job_tbl, which stores archived jobs.

For some reason, goodness knows how it happened, I seem to have duplicated a lot of records, although they do all have a unique identity key so they are not asolutely identical.

If I run the command:
select distinct job_id, job_no, inc_id, description from
dbo_arch_job_tbl
I get around 4200 rows returned.
If I run the command
select distinct job_no, inc_id, description from
dbo_arch_job_tbl
I get only around 2400 rows returned. This is because there are many rows with identical job_no, inc_id and description. But there should not be. I wish to delete all the duplicate rows with identical job_no, inc_id and description, leaving only one row, not duplicated.

Please help, all the delete statements I come up with end up with me deleting too many rows so that the job_no, inc_id and descriptions dissappear completely.

Thanks x x
Jessie

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-09 : 10:16:12

How about:

DELETE
FROM dbo_arch_job_tbl
WHERE job_id NOT IN
(SELECT MIN(job_id)
FROM YourTable
GROUP BY job_no, inc_id, description)

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2004-11-09 : 10:16:36

Hi,

Try this

a) Select distinct from the table.
b) Insert the above result set into a temp table.
c) Delete the duplicate records from the primary table.
d) Insert the records from the temp table to the primary table

Please make sure that u handle the errors and maintain the transaction.

Regards
Sachin Samuel

littlejessihaha
Starting Member

5 Posts

Posted - 2004-11-10 : 09:05:31

Oh my,

I tried option one:
DELETE FROM dbo_arch_job_tbl WHERE job_id NOT IN (SELECT MIN(job_id) FROM YourTable GROUP BY job_no, inc_id, description)

There were still lots of duplicates, so I modified the code a bit and BANG I lost loads of records. Thanks for the suggestions guys. I wish I had gone for option two.

Anyone know how to recover a db (or db table) from the transaction logs?

xx

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-10 : 09:16:27

SQL Tip

ALWAYS start your SQL with SELECT to ensure that you are getting the required results, then once you are happy with the results and are confident that it is correct change the SELECT to DELETE/UPDATE

I also (if it is feasible) do a sideways copy of the table and have a current backup to fall back on

Andy

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-10 : 09:17:32

Since we're on an online forum and you didn't post DDL or sample data, these were only IDEAS for you to test in a non-production environment. Remember that from now on. If we can't test, there is no guarantee.

You can download a trial version of Lumigent Log Explorer (www.lumigent.com) or Log PI (www.logpi.com) -- one of those two products should be able to help you recover the rows.

Next time, post DDL and sample data, and test first. Since we're not there with you, we can only work with as much (or as little) as you give us.

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-10 : 09:19:23

Andy,

Another thing I sometimes do during testing is:

BEGIN TRANSACTION
DELETE FROM ..
WHERE ..

Then I can look at the table at my leisure and COMMIT / ROLLBACK as I see fit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources