SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete Row from one table if other table with multiple rows meet the condition


Delete Row from one table if other table with multiple rows meet the condition

Author
Message
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62025 Visits: 17954
RamSteve (10/9/2013)
here applying condition 99 is not needed as i need to delete all the rows in TableA with the same condition WHERE deleteflag is 1 for all the products


Well so far you have seen several possibilities posted. We don't know if any of these worked for you. If they didn't we have no way of testing because still haven't seen table structures with some sample data. We are all shooting in the dark here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
RamSteve
RamSteve
Right there with Babe
Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)Right there with Babe (792 reputation)

Group: General Forum Members
Points: 792 Visits: 640
Hi ,
All of the posts here work and the one i gave also works but i am looking for other ways of doing it in a simple manner and with good performance ,

Thanks every one for all the post and keep posting your query here if you have any different ways of doing it .

Thanks again for all your valuable time and knowledge you have shared over here
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62025 Visits: 17954
RamSteve (10/9/2013)
Hi ,
All of the posts here work and the one i gave also works but i am looking for other ways of doing it in a simple manner and with good performance ,

Thanks every one for all the post and keep posting your query here if you have any different ways of doing it .

Thanks again for all your valuable time and knowledge you have shared over here


I can't speak for anybody else but I am not a big fan of trying to think of ways a query might work against a non existent table. If you want the fastest way to accomplish this you need to post the details of your table(s). Otherwise, glad you were able to figure out something that works.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
nitinkachhwaha
nitinkachhwaha
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 34
Hi,

Please find the query that will delete the record for ConsumerID = 99 from table A.

BEGIN TRAN

SELECT * FROM TableA

Delete TableA from TableA a
where ConsumerID = 99 and
exists(select 1 from TableB b where a.ConsumerID = b.ConsumerID and b.DeleteFlag = 1)

SELECT * FROM TableA

ROLLBACK/COMMIT
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search