Solved

How to backout records from insert with delete

Posted on 2008-06-17
9
200 Views
Last Modified: 2010-03-20
I ran the following insert query:
Insert into FileOne
   (CNumber,
     ID,
    DCode)
Select
    a.Cnumber
   a.ID
   a.DCode
FROM PFILE a
    INNER JOIN TFile b
   on a.ID = b.ID

The problem is that the field DCode in PFILE a contained nulls which violated the constraint in FileOne that says nulls are not allowed on DCode.  The query ran until it hit the nulls, then errored out.  I've changed the query to add "WHERE DCode is not Null"  but now there are records in FileOne that have been inserted and I can't rerun the query because there are duplicates in FileOne.  I think I can safely back out the records I added with the following:

DELETE * From FileOne a
     INNER JOIN Pfile b
    ON a.ID = b.ID

Does this sound right?  Will this delete work to remove only the records inserted in the first query?


0
Comment
Question by:dkcoop03
  • 4
  • 4
9 Comments
 
LVL 2

Expert Comment

by:howyue
ID: 21809801
u don need to backout ur record that u have previously inserted.
in ur select statement u just select records that are not in table FileOne

Insert into FileOne (CNumber,ID,DCode)

Select a.Cnumber, a.ID, a.DCode

FROM PFILE a

INNER JOIN TFile b on a.ID = b.ID

WHERE c.Cnumber NOT IN (Select CNumber From FileOne) --specify ur primary key here

Open in new window

0
 
LVL 1

Expert Comment

by:sqlconsumer
ID: 21810994
Are you sure that the Records were commited to FileOne? If your query failed the transaction should get roled back.

To check...

SELECT * FROM FileOne f1
WHERE f1.ID IN
(SELECT fp.ID FROM PFILE pf INNER JOIN TFILE tf ON pf.ID = tf.ID)

This will return any records that have been added by your query.

You can then use the sub query for your delete statement. Always create a select query first, then delete.

Hope this helps
0
 

Author Comment

by:dkcoop03
ID: 21812335
If the table was rolled back wouldn't I be allowed to rerun the query?  I'm running the query to check if records were added now but it will probably take awhile.
0
 
LVL 1

Expert Comment

by:sqlconsumer
ID: 21812473
Yep you should be able to rerun the corrected query.

How long does the select query take to run? do you have any indexes on the table?
0
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.

 

Author Comment

by:dkcoop03
ID: 21812603
There are about 130million records but the query finished and yes the records are in the table.  So now if I use the subquery ((SELECT fp.ID FROM PFILE pf INNER JOIN TFILE tf ON pf.ID = tf.ID) I should be able to remove the records correct?  Yes there are indexes on the table.
0
 

Author Comment

by:dkcoop03
ID: 21812767
I think this code will do it if I put it at the end of the insert query
WHERE c.IDr NOT IN (Select ID From FileOne) --specify ur primary key here

But I'm confused about which primary key I'm testing.  Here it says c.Cnumber but there is no alias c.  Should it be a.Cnumber -- am I testing against the key from PFILE and does it matter that I've joined PFile to Tfile for the original insert
 
0
 
LVL 1

Expert Comment

by:sqlconsumer
ID: 21812778
Yep use the sub query in your WHERE clause and you will only delete these records.

I recommend reading up on BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION. They might save you a lot of bother in the future.
0
 

Author Comment

by:dkcoop03
ID: 21812984
I will definitely read up on these topics.  I would like to confirm what I should be doing:

FROM PFILE a
    INNER JOIN TFile b
   on a.ID = b.ID
  WHERE FileOne.ID not in
(SELECT fp.ID FROM PFILE pf INNER JOIN TFILE tf ON pf.ID = tf.ID)

Does this look correct?
0
 
LVL 1

Accepted Solution

by:
sqlconsumer earned 500 total points
ID: 21813569
Not quite,

Try something more like

DELETE FROM FileOne
WHERE FileOne.ID IN
(SELECT fp.ID FROM PFILE pf INNER JOIN TFILE tf ON pf.ID = tf.ID)

The number of rows deleted should be exactly the same as the number of rows return by the query

SELECT * FROM FileOne f1
WHERE f1.ID IN
(SELECT fp.ID FROM PFILE pf INNER JOIN TFILE tf ON pf.ID = tf.ID)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now