t sql delete duplicates rows from 2 tables

Question

I have 2 tables:

FirstTable
SecondTable

FirstTable has these columns

sales_ID varchar(250)
product_ID int

SecondTable has these columns

sales_ID varchar(250)
product_ID int
CreateDate datetime

If FirstTable's sales_ID and product_ID match to SecondTable's sales_Id and product_ID, and also if SecondTable's CreatedDate is 07.04.2015, I need to delete the row from SecondTable

I tried like this:

delete from SecondTable
WHERE sales_Id IN (SELECT sales_Id FROM FirstTable) 
  and product_ID IN (SELECT product_ID FROM FirstTable) 
  and CreatedDate = '07-04-2015'

However it deletes all rows and also it is not correct (not working) query according to what I want.

How can I delete duplicate rows if product_ID equals product_ID and sales_ID equal sales_ID and CreatedDate equals 07.04.2015 from SecondTable?

Hope you understand my bad english

Thanks.

Simon UK · Accepted Answer · 2015-04-08 14:07:28Z

3

That's because it's purely checking for the presence of the sales id in the second table and the product id, seperately, not on the same record.

Try for example

Delete T2
FROM firsttable AS T1
INNER JOIN secondtable T2
on T1.sales_id = T2.sales_id
AND T1.product_id = T2.product_id
WHERE T1.CreatedDate = '20150407'  -- assuming you mean 7th April?

answered Apr 8, 2015 at 14:07

Simon UK

1941 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kritner · Accepted Answer · 2015-04-08 14:07:33Z

1

try this:

delete from secondtable
from secondTable st
inner join firstTable ft on st.sales_id = ft.sales_id
    and st.product_id = ft.product_id
    and st.CreatedDate = '2015-07-04'

note you'll probably want to wrap the above in a transaction to make sure you're not deleting unwanted records - or turn the statement into a select:

select st.*
from secondTable st
inner join firstTable ft on st.sales_id = ft.sales_id
    and st.product_id = ft.product_id
    and st.CreatedDate = '2015-07-04'

answered Apr 8, 2015 at 14:07

Kritner

13.8k11 gold badges53 silver badges76 bronze badges

Comments

Sheldon Neilson · Accepted Answer · 2015-04-08 14:07:33Z

1

DELETE 
  FROM SecondTable t2
  JOIN FirstTable t1
    ON t1.sales_ID = t2.sales_ID
   AND t1.product_ID = t2.product_ID
   AND t1.CreatedDate = '2015-07-04'

answered Apr 8, 2015 at 14:07

Sheldon Neilson

8024 silver badges8 bronze badges

Collectives™ on Stack Overflow

t sql delete duplicates rows from 2 tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related