October 7, 2013 at 11:20 am
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
October 7, 2013 at 11:54 am
October 7, 2013 at 12:27 pm
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 ProductCan 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 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/
October 7, 2013 at 12:39 pm
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 )
)
October 7, 2013 at 12:50 pm
RamSteve (10/7/2013)
Hi i do not want to delete consumerID 100 as deleteflag is 0 for one productand 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 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/
October 7, 2013 at 12:53 pm
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 )
)
October 7, 2013 at 1:03 pm
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 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/
October 7, 2013 at 1:06 pm
I guess this Query will delete all rows in TableA
October 7, 2013 at 1:08 pm
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/
October 7, 2013 at 1:10 pm
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 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/
October 7, 2013 at 1:15 pm
In TableA We don't have Product Column, It only in TableB
October 7, 2013 at 1:19 pm
RamSteve (10/7/2013)
In TableA We don't have Product Column, It only in TableB
You really need to quote the original post in your responses so we have a clue what you are referring to.
We are all shooting in the dark here because we have no knowledge of your system and you didn't provide ddl for us. Please take a few minutes and read the first article in my signature.
_______________________________________________________________
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/
October 7, 2013 at 1:19 pm
You need to provide some DDL for this problem. How about some create table statements with actual column names and real data with expected results.
October 7, 2013 at 11:43 pm
Hi,
Try with this code
delete from tableA where ConsumerID in(
select ConsumerID from tableB where ConsumerID not in(
select ConsumerID from tableB
where DeleteFlag = 0))
October 9, 2013 at 3:10 am
CREATE TABLE [A]
(consumerID INT, XXXX INT, AAAAA INT, BBBB INT)
CREATE TABLE
(consumerID INT, Product INT, DeleteFlag BIT, DDDD INT)
INSERT INTO A VALUES (99,1,2,3)
INSERT INTO A VALUES (100,2,3,4)
INSERT INTO B VALUES (99,1,1,4)
INSERT INTO B VALUES (99,2,1,5)
INSERT INTO B VALUES (99,1,0,6)
INSERT INTO B VALUES (99,5,1,3)
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply