How to delete records from a table if they don’t meet a condition in another table

I have two tables, company and company_name. company contains the columns: company_id(primary key) and company_name, while company_name contains the columns: company_id(foreign key from company table), company_name, and is_proper. Currently, the company table is filled with several records, but I wish to only keep company_names that appear in both tables and records where is_proper = 1.

Example company Table

company_id    company_name
1             Apple
2             Apple Inc.
3             Fcebook   
4             Facebook
5             Facebook Inc. 
6             Google Inc.

Example company_name Table:

company_id    company_name    is_proper
2             Apple           0
2             Apple Inc.      1
5             Fcebook         0
5             Facebook        0
5             Facebook Inc.   1
6             Google Inc.     1

What I want company Table to look like:

company_id    company_name
2             Apple Inc.
5             Facebook Inc.
6             Google Inc.

If someone knows a SQL command to do this, it would be very helpful. Thanks in advance.

Answer

With NOT EXISTS:

delete c from company c
where not exists (
  select 1 from company_name 
  where company_id = c.company_id and company_name = c.company_name and is_proper = 1
)

See the demo.
Results:

> company_id | company_name 
> ---------: | :------------
>          2 | Apple Inc.   
>          5 | Facebook Inc.
>          6 | Google Inc.

Advertisement

Answer