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

Question

Post reply

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

Viewing 9 posts - 16 through 23 (of 23 total)

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

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 1

DELETE A FROM A INNER JOIN B ON A.consumerID = B.consumerID WHERE A.consumerID = 99 AND B.deleteflag = 1

gudukrmca SSC Rookie Points: 33 More actions · Answer 2

Using Co-related query:

DELETE FROM tablea

WHERE ConsumerID IN

(SELECT ConsumerID FROM Tableb b WHERE tablea.ConsumerID=b.ConsumerID and b.DeleteFlag=1)

AND ConsumerID=99

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 3

Using a common_table_expression can make it more readable;

;WITH ToDelete (ConsumerId) AS (

SELECTConsumerId FROM Table_B WHERE DeleteFlag = 1

EXCEPT

SELECTConsumerId FROM Table_B WHERE DeleteFlag = 0

)

DELETETABLE_A

FROMTABLE_A INNER JOIN

ToDelete ON TABLE_A.ComsumerId = ToDelete.ConsumerId

gudukrmca SSC Rookie Points: 33 More actions · Answer 4

I believe Louis Hillebrand's query deletes all record. Need to add ConsumerID=99 in condition.

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 5

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 7

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 SSC Guru Points: 286590 More actions · Answer 8

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

nitinkachhwaha SSC Enthusiast Points: 114 More actions · Answer 9

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