help with a query

[vasilanthropos]

Hello. I want help with a query. I have 2 tables and i want to check if table1.field1= table2.field1 and table1.field2=table2.field2 .The records that don't satisfy this condition must be deleted. I have written the following query for selecting the values that should remain in the tables. How can i delete the others?

SELECT *
FROM capitals, country
WHERE capitals.ABR = country.Code2
AND capitals.Country = country.Name

[russell]

what dbms?

deleted from which table... 1 or 2 or both?

[vasilanthropos]

in mysql. I want to delete records from both tables

[chazzy]

is there some kind of shared primary key? a foreign key or anything governing the relationship of this table?

[NightShift58]

Does this do the job for you?select 'capitals' as tbl_name, id FROM capitals
where (capitals.ABR not in (select country.Code2 as ABR from country))
or (capitals.Country not in (select country.Name as Counry from country))
UNION
select 'country' as tbl_name, id FROM country
where (country.Code2 not in (select capitals.ABR from capitals))
or (country.Name not in (select capitals.Country from capitals))
This returns a recordset consisting of 2 fields, containing the table name and the record id for all "mismatches" between the two tables.

Or did I go astray somewhere?