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 Monday, October 7, 2013 11:20 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 I have 2 Tables as below , I need to delete TableA row with ConsumerID = 99 as my condition would be to delete the rows in Table A if DeleteFlag in Table B is 1 for the columns ConsumerID and Product
Can some one help me deleting the Table A With Consumer ID = 99 with sql script


thanks in Advance :)


Table A :
ConsumerID, xxxx, AAAAA, BBBB
99 1 2 3
100 2 3 4

Table B :
ConsumerID, Product, DeleteFlag, dddd

99 1 1 4
99 2 1 5
100 1 0 6
100 5 1 3
Post #1502277
Posted Monday, October 7, 2013 11:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:53 AM
Points: 597, Visits: 937
This looks like a homework assignment or something. You need to look up how to do a join in a delete statement or using the EXISTS statement.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502285
Posted Monday, October 7, 2013 12:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
RamSteve (10/7/2013)
Hi I have 2 Tables as below , I need to delete TableA row with ConsumerID = 99 as my condition would be to delete the rows in Table A if DeleteFlag in Table B is 1 for the columns ConsumerID and Product
Can some one help me deleting the Table A With Consumer ID = 99 with sql script


thanks in Advance :)


Table A :
ConsumerID, xxxx, AAAAA, BBBB
99 1 2 3
100 2 3 4

Table B :
ConsumerID, Product, DeleteFlag, dddd

99 1 1 4
99 2 1 5
100 1 0 6
100 5 1 3


There are obviously some additional business rules missing from your description. Why would you not also delete ConsumerID 100? There is a row with DeleteFlag of 1 in TableB.

As previously stated, this looks a lot like homework. We will help you figure out a solution but we don't do homework for you. If you we do the work, you don't learn anything.


_______________________________________________________________

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 #1502301
Posted Monday, October 7, 2013 12:39 PM
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 i do not want to delete consumerID 100 as deleteflag is 0 for one product
and moreover this is not a home work , but i am trying to do it in a simple way for the below query

DELETE Table A
FROM TableA a
INNER JOIN TableB b ON b.ConsumerID = a.ConsumerID
WHERE a.ConsumerID IN
(
SELECT ConsumerID FROM TableB WHERE DeleteFlag= 1 AND ConsumerID NOT IN (SELECT Consumer_ID FROM Visitor_By_Brand WHERE Delete_Flag =0 )
)



Post #1502310
Posted Monday, October 7, 2013 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
RamSteve (10/7/2013)
Hi i do not want to delete consumerID 100 as deleteflag is 0 for one product
and moreover this is not a home work , but i am trying to do it in a simple way for the below query

DELETE Table A
FROM TableA a
INNER JOIN TableB b ON b.ConsumerID = a.ConsumerID
WHERE a.ConsumerID IN
(
SELECT ConsumerID FROM TableB WHERE DeleteFlag= 1 AND ConsumerID NOT IN (SELECT Consumer_ID FROM Visitor_By_Brand WHERE Delete_Flag =0 )
)



Your query as posted here would still delete 100 assuming there is not a record in Visitor_By_Brand where Delete_Flag = 0 for that consumer.


_______________________________________________________________

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 #1502315
Posted Monday, October 7, 2013 12:53 PM
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
my query would be as below

DELETE Table A
FROM TableA a
INNER JOIN TableB b ON b.ConsumerID = a.ConsumerID
WHERE a.ConsumerID IN
(
SELECT ConsumerID FROM TableB WHERE DeleteFlag= 1 AND ConsumerID NOT IN (SELECT ConsumerID FROM TableB WHERE DeleteFlag =0 )
)
Post #1502316
Posted Monday, October 7, 2013 1:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
I think you could use this for the same. It is a bit simpler but should accomplish the same thing.

DELETE TableA
FROM TableA a
WHERE a.ConsumerID IN
(
SELECT ConsumerID
FROM TableB
group by ConsumerID
HAVING MIN(DeleteFlag) = 1
)



_______________________________________________________________

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 #1502318
Posted Monday, October 7, 2013 1:06 PM
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
I guess this Query will delete all rows in TableA
Post #1502319
Posted Monday, October 7, 2013 1:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:21 PM
Points: 401, Visits: 1,715
There is a little bit of guessing here.

I need to delete TableA row with ConsumerID = 99 as my condition would be to delete the rows in Table A if DeleteFlag in Table B is 1 for the columns ConsumerID and Product


Based on your original request, would this be the right interpretation?

if A.ID = B.ID and A.xxxx = B.Product_ID and B.DeleteFlag = 1 then
Delete from TableA
end

If so, then this could work:

delete a
from TableA a
inner join TableB b on b.ID = a. ID and b.PID = a.xxxx --guessing this is a product id
where b.DeleteFlag = 1



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1502320
Posted Monday, October 7, 2013 1:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
RamSteve (10/7/2013)
I guess this Query will delete all rows in TableA


Which query?


_______________________________________________________________

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 #1502321
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse