Delete Records From The Sql Server Table

[svgeorge]

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

[andreasblixt]

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;

[Klymer]

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

first:
all deletes should be made within a begin transaction ... <delete statement> ... rollback block. you'll be able to see how many records are affected without harm. and personally: if i delete data, i do first a backup with a select into statement for the affected data
for your problem:
you have to make a correlated subselect, which is simply said a connection between your outer (delete) statement and the inner (select) statement
you could do this by:

DELETE FROM dbo.Payment_Placement_AIMS
WHERE EXISTS
(select * from Payment_Placement_AIMS p INNER JOIN
TESTING ON p.JC_ID = TESTING.JC_ID
WHERE p.Date_Stamp > '10/21/2007 12:00:00 AM' AND p.[pk] = dbo.Payment_placements_AIMS.[pk] ) AND (Payment_Placement_AIMS.EMP_TYPE = 'First employment'));

ps: this is untested code, :blush: so use my precautions
hth