Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Delete Row from one table if other table with multiple rows meet the condition Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 9:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's 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)
Post #1503183
Posted Wednesday, October 9, 2013 9:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 1, 2014 12:23 PM
Points: 166, Visits: 578
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
Post #1503188
Posted Wednesday, October 9, 2013 9:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's 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)
Post #1503190
Posted Thursday, October 10, 2013 4:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 5:14 AM
Points: 84, Visits: 23
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
Post #1503442
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse