Select and then delete how? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select and then delete how?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-22 : 09:43:26

HI I Have this sql that will select the rows in the customer table that do NOT have any transactions. Now I need to then delete these within this SQL I am not sure how to do this so there are no mistakes,

Select
*
From
Customers As CustomerInfo
Left Outer Join Transactions As TransInfo On CustomerInfo.CustomerID = TransInfo.CustomerId
Where
TransInfo.TransactionId Is Null

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-03-22 : 09:58:00

delete from customer
where not exists (select * from transinfo where CustomerInfo.CustomerID = TransInfo.CustomerId)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-22 : 09:59:39

[code]Select
*
delete
From
Customers
from Customers As CustomerInfo
Left Outer Join Transactions As TransInfo On CustomerInfo.CustomerID = TransInfo.CustomerId
Where
TransInfo.TransactionId Is Null[/code]If you have only a few records, it may not make any difference, but it may be more efficient to rewrite it as[code]delete from Customers
where not exists( select * from Transactions where Transactions.CustomerId = Customers.CustomerId) [/code]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-22 : 10:00:08

Andrew, you beat me to it!! :--)
(But, only by 1 minute and 39 seconds. So I am rounding it to 10 minutes, which means you really didn't beat me:--)

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-03-22 : 10:24:18

THank you both, I just have one issue there is a referential contraint, how would you handle this?
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_CustomerMappings_Customers". The conflict occurred in database "MyTestDB", table "dbo.CustomerMappings", column 'CustomerId'.
The statement has been terminated.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-22 : 11:28:02

This means that in the CustomerMappings table there are references to those customers you were going to delete. So, first you need to delete those rows from the CustomerMappings table (assuming that that is what you want to do.)

You can use a delete statement similar to the one you were going to use rows from the Customers table.

delete from dbo.CustomerMappings
where not exists( select * from Transactions inner join Customers
	on Transactions.CustomerId = Customers.CustomerId
	where Customers.CustomerId = CustomerMappings.CustomerId)

You should be able to bypass Customers table altogether when deleting from the CustomerMappings table as in

delete from dbo.CustomerMappings
--select * from dbo.CustomerMappings
where not exists (select * from Transactions where Transactions.CustomerId = CustomerMappings.CustomerId)

But, when it comes to deleting, I am paranoid, so I would first run the select statement that I have commented out to see what you are deleting. The reason I hesitate on the second method is that if the constraint was added sometime after the tables were created and populated, there may be orphaned rows that you would delete if you did it via the second query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources