I have 2 tables with no primary-foreign key relationship. I want to delete a record(row to be more specific) in Table1 when count of a row in Table2 reaches a certain value.
Table1
ID Name
1 name1
2 name2
Table2
ID Name Country
1 name1 US
2 name2 China
3 name2 Germany
4 name1 KSA
If name1 count in Table2 reaches 40 then delete name1 from Table1
Hope i have explained it all. Thanks in advance
From what I understood I came up with this solution. I'm not sure how efficient this is but I guess does the trick.
DELETE FROM Table1 where Name IN (select Name from Table2 group by [Name] having count(Name)>=40)
Edit: Here you SQL Fiddle that you can try with small set of data. http://sqlfiddle.com/#!6/9bf70/21
If I understood well you need DELETE with JOIN; In the sub-query you get the count / name and then join to table1 and DELETE where count reaches the limit you want.
DELETE T1
FROM tbl1 T1
INNER JOIN (
SELECT
name
,COUNT(name) as cnt
FROM tbl2
GROUP BY name
)T2
ON T1.name = T2.name
WHERE cnt >= 40
Edit: I added some sample data here for you test the code.
DELETE statement; just leave the table name (or use aliases as in my code);Use stored procedure.
Create Procecedure DeleteRecords
(
@recCount int,
@nameField varchar(20)
)
Begin
If((select count(id) from Table2 where [name]=@nameField) >= @recCount)
begin
Delete from Table1 where [name]=@nameField
end
End
You create the INSERT trigger on Table2
create trigger ti_Table2 ON Table2 for insert
as
begin
-- delete rows from table1
delete t1
from
(
-- here it get list of name that exceeds 40 rows
select i.Name
from (
-- get the distinct Name
-- inserted contains newly inserted rows
select distinct i.Name
from inserted i
) i
-- join it to Table2 to find no of rows
inner join Table2 t2 on i.Name = t2.Name
group by i.Name
having count(*) >= 40 -- when exceed 40
) t2
inner join Table1 t1 on t2.Name = t1.Name
end
