CODEWITHSUNDEEP
sqlsql-server
Irfan Ahmed
Irfan Ahmed

Reputation: 1

query for sql server

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

Upvotes: 0

Views: 80

Answers (4)

Valerica
Valerica

Reputation: 1630

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.

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 0

SSD
SSD

Reputation: 1391

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

Upvotes: 0

İlker Çakır
İlker Çakır

Reputation: 149

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

Upvotes: 1

Related Questions