February 27, 2019 at 10:35 am
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
February 27, 2019 at 10:43 am
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 27, 2019 at 11:09 am
I hate WHERE EXISTS. I have always had trepidation with it.....!
--Quote me
February 27, 2019 at 11:16 am
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 28, 2019 at 5:17 am
You checked whether the "WHERE EXISTS" would return any data.
Instead, check what will be deleted by changing "DELETE" into "SELECT *".
March 1, 2019 at 10:41 am
gvoshol 73146 - Thursday, February 28, 2019 5:17 AMYou 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.
March 1, 2019 at 11:04 pm
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
March 11, 2019 at 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.
March 11, 2019 at 9:12 pm
fahey.jonathan - Monday, March 11, 2019 4:45 PMI 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]
...
March 13, 2019 at 7:19 am
Jonathan AC Roberts - Monday, March 11, 2019 9:12 PMfahey.jonathan - Monday, March 11, 2019 4:45 PMI 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply