why did WHERE EXISTS delete the contents of whole table?

Question

why did WHERE EXISTS delete the contents of whole table?

polkadot

SSChampion

Points: 13664
More actions
February 27, 2019 at 10:35 am

#370828

the inner query returns only 36 rows, so I expected the DELETE WHERE EXISTS to delete only 36 rows. But it deleted all data in table :angry: Why?
delete from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy
where exists (
select * from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C join
[Admin].[dbo].[Remove Duplicates_Final] D ON D.[Tracking_Number] = C.[Tracking_Number]
AND d.[Date_record_entered] = C.[Date_record_entered]
AND D.[Date_Submitted_to_Carrier] = C.[Date_Submitted_to_Carrier]
AND D.[ship_method] = C.[ship_method]
AND D.[Channel] = C.[Channel]
AND D.[order_id] = C.[order_id]
AND D.[Claim_Type] = C.[Claim_Type]
AND D.[Reference_Number] = C.[Reference_Number]
AND D.[IMEI_Number] = C.[IMEI_Number]
AND D.[claim_status] = C.[claim_status]
AND D.[DC] = C.[DC]
AND D.[shipto_name_1] = C.[shipto_name_1]
AND D.[Material] = C.[Material]
--Quote me

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101084 More actions · Answer 1

There is no connection between the table you're deleting from and the EXISTS clause. If the EXISTS is ever true for the tables it references, even for just one row, all rows (from the outer table) will be deleted.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

polkadot SSChampion Points: 13664 More actions · Answer 2

I hate WHERE EXISTS. I have always had trepidation with it.....!

--Quote me

ScottPletcher SSC Guru Points: 101084 More actions · Answer 3

Something like this instead:

delete from C from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C where exists ( select * from [Admin].[dbo].[Remove Duplicates_Final] D Where D.[Tracking_Number] = C.[Tracking_Number] AND d.[Date_record_entered] = C.[Date_record_entered] AND D.[Date_Submitted_to_Carrier] = C.[Date_Submitted_to_Carrier] AND D.[ship_method] = C.[ship_method] AND D.[Channel] = C.[Channel] AND D.[order_id] = C.[order_id] AND D.[Claim_Type] = C.[Claim_Type] AND D.[Reference_Number] = C.[Reference_Number] AND D.[IMEI_Number] = C.[IMEI_Number] AND D.[claim_status] = C.[claim_status] AND D.[DC] = C.[DC] AND D.[shipto_name_1] = C.[shipto_name_1] AND D.[Material] = C.[Material] )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

GaryV Hall of Fame Points: 3307 More actions · Answer 4

You checked whether the "WHERE EXISTS" would return any data.

Instead, check what will be deleted by changing "DELETE" into "SELECT *".

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 5

gvoshol 73146 - Thursday, February 28, 2019 5:17 AM
You checked whether the "WHERE EXISTS" would return any data.
Instead, check what will be deleted by changing "DELETE" into "SELECT *".

Definitely but without knowing how many rows are in [Admin].[dbo].[Remove Duplicates_Final] making it SELECT TOP 100 * instead may be a good idea so you don't accidently end up with a query that takes hours to complete

i.e.

SELECT TOP 100 * from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy where exists ( select * from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C join [Admin].[dbo].[Remove Duplicates_Final] D ON D.[Tracking_Number] = C.[Tracking_Number] AND d.[Date_record_entered] = C.[Date_record_entered] AND D.[Date_Submitted_to_Carrier] = C.[Date_Submitted_to_Carrier] AND D.[ship_method] = C.[ship_method] AND D.[Channel] = C.[Channel] AND D.[order_id] = C.[order_id] AND D.[Claim_Type] = C.[Claim_Type] AND D.[Reference_Number] = C.[Reference_Number] AND D.[IMEI_Number] = C.[IMEI_Number] AND D.[claim_status] = C.[claim_status] AND D.[DC] = C.[DC] AND D.[shipto_name_1] = C.[shipto_name_1] AND D.[Material] = C.[Material]

Doing this means it will be immediately clear that you have the query wrong as this will return all rows up to 100 total from the table rather than the 36 you were expected.
If you always develop delete queries initially as selects you can be confident that when you change it to a DELETE that it will work as you expect.

polkadot SSChampion Points: 13664 More actions · Answer 6

yes, this is good advice. From now on I will use DELETE TOP (10) .....usually I do the SELECT first before I run the DELETE but *this time* for some crazy reason, and even using WHERE EXISTS for the first time to delete something, I only checked the inner query.
AAAAAH.
:sick:

--Quote me

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 7

I think you are trying to delete from Table1 where matching rows exist in Table2. If that is correct, then I think this is what you want:
delete from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C join [Admin].[dbo].[Remove Duplicates_Final] D ON D.[Tracking_Number] = C.[Tracking_Number] AND d.[Date_record_entered] = C.[Date_record_entered] AND D.[Date_Submitted_to_Carrier] = C.[Date_Submitted_to_Carrier] AND D.[ship_method] = C.[ship_method] AND D.[Channel] = C.[Channel] AND D.[order_id] = C.[order_id] AND D.[Claim_Type] = C.[Claim_Type] AND D.[Reference_Number] = C.[Reference_Number] AND D.[IMEI_Number] = C.[IMEI_Number] AND D.[claim_status] = C.[claim_status] AND D.[DC] = C.[DC] AND D.[shipto_name_1] = C.[shipto_name_1] AND D.[Material] = C.[Material]
The first FROM clause tells you which table is being deleted, and the second FROM tells how Table1 is related to Table2.

Jonathan AC Roberts SSCoach Points: 18339 More actions · Answer 8

fahey.jonathan - Monday, March 11, 2019 4:45 PM
I think you are trying to delete from Table1 where matching rows exist in Table2. If that is correct, then I think this is what you want:
delete from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C join [Admin].[dbo].[Remove Duplicates_Final] D ON D.[Tracking_Number] = C.[Tracking_Number] AND d.[Date_record_entered] = C.[Date_record_entered] AND D.[Date_Submitted_to_Carrier] = C.[Date_Submitted_to_Carrier] AND D.[ship_method] = C.[ship_method] AND D.[Channel] = C.[Channel] AND D.[order_id] = C.[order_id] AND D.[Claim_Type] = C.[Claim_Type] AND D.[Reference_Number] = C.[Reference_Number] AND D.[IMEI_Number] = C.[IMEI_Number] AND D.[claim_status] = C.[claim_status] AND D.[DC] = C.[DC] AND D.[shipto_name_1] = C.[shipto_name_1] AND D.[Material] = C.[Material]
The first FROM clause tells you which table is being deleted, and the second FROM tells how Table1 is related to Table2.

I prefer to write that statement like this:
delete C from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C join [Admin].[dbo].[Remove Duplicates_Final] D ON D.[Tracking_Number] = C.[Tracking_Number] AND d.[Date_record_entered] = C.[Date_record_entered] AND D.[Date_Submitted_to_Carrier] = C.[Date_Submitted_to_Carrier] ...

sgmunson SSC Guru Points: 110640 More actions · Answer 9

Jonathan AC Roberts - Monday, March 11, 2019 9:12 PM
fahey.jonathan - Monday, March 11, 2019 4:45 PM
I think you are trying to delete from Table1 where matching rows exist in Table2. If that is correct, then I think this is what you want:
delete from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C join [Admin].[dbo].[Remove Duplicates_Final] D ON D.[Tracking_Number] = C.[Tracking_Number] AND d.[Date_record_entered] = C.[Date_record_entered] AND D.[Date_Submitted_to_Carrier] = C.[Date_Submitted_to_Carrier] AND D.[ship_method] = C.[ship_method] AND D.[Channel] = C.[Channel] AND D.[order_id] = C.[order_id] AND D.[Claim_Type] = C.[Claim_Type] AND D.[Reference_Number] = C.[Reference_Number] AND D.[IMEI_Number] = C.[IMEI_Number] AND D.[claim_status] = C.[claim_status] AND D.[DC] = C.[DC] AND D.[shipto_name_1] = C.[shipto_name_1] AND D.[Material] = C.[Material]
The first FROM clause tells you which table is being deleted, and the second FROM tells how Table1 is related to Table2.
I prefer to write that statement like this:
delete C from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C join [Admin].[dbo].[Remove Duplicates_Final] D ON D.[Tracking_Number] = C.[Tracking_Number] AND d.[Date_record_entered] = C.[Date_record_entered] AND D.[Date_Submitted_to_Carrier] = C.[Date_Submitted_to_Carrier] ...

Pretty much most folks prefer that format. Having the word FROM appear twice is just plain confusing, even if it's "allowed".

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)