Delete Records From The Sql Server Table
I AM TRYING TO DELETE RECORDS FROM THE SQL SERVER TABLE,
I AM USING THE COMMAND BELOW TO DELETE THOSE 71 RECORDS BUT MY WHOLE TABLE 1198 records GOT DELETED AND HAD TO RECOVER FROM BACKUP..
SO WHAT IS WRONG WITH THE SYNTAX GIVEN BELOW
Please give me the correct syntax please I am reffering to the table to be updated as dbo.Payment_Placement_AIMS and fetching those 71 records to be deleted in the subquery select statement using the TESTING view as join to identify those records to delete
DELETE FROM dbo.Payment_Placement_AIMS
WHERE EXISTS
(select * from Payment_Placement_AIMS INNER JOIN
TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM') AND (Payment_Placement_AIMS.EMP_TYPE = 'First employment'));
Thanks in Advance
George
[891 byte] By [
svgeorge] at [2007-11-20 11:48:45]
# 1 Re: Delete Records From The Sql Server Table
EXISTS () is a boolean operation, and will either be true or false. So basically, if it's false it doesn't delete anything and if it's true it deletes everything.
The simplest way to change your query to get it to work is to change WHERE EXISTS (SELECT * ...) to WHERE IdField IN (SELECT IdField ...)
Basically you use IN instead of EXISTS, which checks if a value is in a collection of values (which can either be a comma-delimited list of static values or a sub-query) and then you compare your primary key field (IdField in my example) with all the primary key fields in the sub-query (which has all the rows you want to delete.)
Obviously, IdField would be replaced with the name of your primary key field in the Payment_Placement table.
This should also work:
DELETE FROM dbo.Payment_Placement_AIMS
INNER JOIN TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM' AND Payment_Placement_AIMS.EMP_TYPE = 'First employment';
And this:
WITH RowsToDelete
AS (select * from Payment_Placement_AIMS INNER JOIN
TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM' AND Payment_Placement_AIMS.EMP_TYPE = 'First employment')
DELETE FROM RowsToDelete;
