How to backout records from insert with delete
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?
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?
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
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
ASKER
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.
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?
How long does the select query take to run? do you have any indexes on the table?
ASKER
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.
ASKER
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
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
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.
I recommend reading up on BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION. They might save you a lot of bother in the future.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in ur select statement u just select records that are not in table FileOne
Open in new window