How to remove common records found in 2 tables?

how to compare two columns and delete matches in excel?
delete excel rows based on another list
how to delete duplicate rows in excel
sql compare two tables and find records without matches
how to delete duplicate entries in excel
excel remove duplicate rows based on two columns
how to remove duplicate pairs in excel
delete matching rows in excel

For example:

table 1
        x   y
        2   3
        1   2
        8   9

table 2
        x   y
        2   3
        8   9

So here I need to remove the common field found in both the table result should be

Result
      x   y
      1   2

only i should get a unique row

You can use NOT EXISTS :

SELECT t1.*
FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.X = t1.X AND t2.Y = t1.Y);

Remove common records between two data sets, The countifs function counts common records from two tables. Row 2 in sheet1 is found once in sheet 2 and so on. Deselect 1. Click OK. If you omit T1 table, the DELETE statement only deletes rows in T2 table. Similarly, if you omitT2 table, the DELETE statement will delete only rows in T1 table. The expression T1.key = T2.key specifies the condition for matching rows between T1 andT2 tables that will be deleted.

You can also use NOT EXISTS operator which is more efficient as it also considers NULL values while comparison.

select * from table1 A
where not exists (select name from table2 B where A.name=B.name)

Multiple Ways to Remove Duplicates from SQL Tables – {coding}Sight, The presence of duplicate rows is a common issue that SQL Self-Referencing method 2 to find duplicate students having same name, course� SQL delete duplicate Rows using Common Table Expressions (CTE) We can use Common Table Expressions commonly known as CTE to remove duplicate rows in SQL Server. It is available starting from SQL Server 2005. We use a SQL ROW_NUMBER function, and it adds a unique sequential row number for the row.

Try this:

Demo 1 Demo 2

select x, y
from
(
  select x, y
  from table1
   union all
  select x, y
  from table2
) i
group by x, y
having count(*) = 1

[Solved] Single SQL Query to delete records in two tables , Try this: Hide Copy Code. DELETE c.*, sb.* FROM Category c LEFT JOIN subCategory sb ON c.cat_id = sb.cat_id WHERE c.cat_id = 1 Now when u delete a record from the master table all other details table record based on the deleting rows primary key value, will be deleted automatically. So in that case a single delete query of master table can delete master tables data as well as child tables data.

Assuming the task is "delete duplicated record from BOTH TABLES accounting NULLs":

DELETE t1.*, t2.*
FROM table1 t1
JOIN table2 t2 ON t1.x <=> t2.x
              AND t1.y <=> t2.y;

fiddle


Assuming the task is "delete duplicated record from both tables NOT accounting NULLs":

DELETE t1.*, t2.*
FROM table1 t1
JOIN table2 t2 USING (x, y);

fiddle


If the task is "delete duplicated record from one table and store them in another table" then edit DELETE t1.*, t2.* and store only those table which records must be deleted, for example, DELETE t1.*.

Different ways to SQL delete duplicate rows from a SQL Table, In the output above, we have two duplicate records with ID 1 and 3. Emp ID 1 has two occurrences in the Employee table; Emp ID 3 has three� In the Show Table dialog box, double-click each table from which you want to delete records, and then click Close. The table appears as a window in the upper section of the query design grid. From the list of fields, double-click the asterisk ( * ) to add all of the fields in the table to the design grid.

Assuming this is to remove duplicate from a table that exist in another table

if all colums have non null value then:-

DELETE t1 
FROM tbl_test_1 t1
INNER JOIN  tbl_test_2 t2
ON t1.col_1 = t2.col_1
AND t1.col_2 = t2.col_2
WHERE t2.id is  not null

Here if a particualr coulm from both table are NULL , we miss that. So to catch colm match with NULL as well, try this:-

#to check before deletion
SELECT * , 
concat_ws("_",COALESCE(t1.col_1, 'null'),COALESCE(t1.col_2, 'null')),
concat_ws("_",COALESCE(t2.col_1,'null'),COALESCE(t2.col_2,'null')) 
FROM tbl_test_1  t1
left JOIN  tbl_test_2 t2
ON concat_ws("_",COALESCE(t1.col_1, 'null'),COALESCE(t1.col_2, 'null'))= concat_ws("_",COALESCE(t2.col_1, 'null'),COALESCE(t2.col_2, 'null'))

WHERE t2.id is  not null

delete query

#delete query
DELETE t1 
FROM tbl_test_1  t1
left JOIN  tbl_test_2 t2
ON concat_ws("_",COALESCE(t1.col_1, 'null'),COALESCE(t1.col2, 'null'))= concat_ws("_",COALESCE(t2.col_1, 'null'),COALESCE(t2.col2, 'null'))
WHERE t2.id is  not null

Find and Remove Duplicate Rows from a SQL Server Table, We will consider two cases in this tip: The first case is when a SQL Server table has a primary key (or unique index) and one of the columns� In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The image above shows you highlighted records in List 1 that also exists in sheet2. You can also use a formula to extract shared records or an Excel defined table, if you prefer that.

Compare two tables and find records without matches, Use the Find Unmatched Query in Access to compare two tables and identify query's design to add or remove fields, or to add joins between the two tables (to Only those records with matching data in the fields in both tables are included� I often do things like the following made-up example. (This example is from Informix SE running on Linux.) The point of of this example is to delete all real estate exemption/abatement transaction records -- because the abatement application has a bug -- based on information in the real_estate table.

Find duplicate records with a query - Access, Learn how to find duplicate data in your Access data using a query. be either multiple tables containing the same data or two records containing just some After you locate duplicate records, you can either edit or Delete duplicate records � And filters them, keeping only the records where the Pupil Name matches the name on the Marks table. The technical name is a Join. This is often referred to as a "Multiplication", because the number of records in the intermediary table (before filtering) is a multiplication of the two tables:

Finding Duplicates with SQL, Also see How to remove duplicate rows from a table - Microsoft Knowledge base article count how many records in two tables and return sum of both tables.

Comments
  • You say remove but your result could be interpreted as a select where you get a unique row from either table or it could be interpreted as meaning delete the non unique rows from both tables. Which is it?
  • Do you want (1) to select rows from table1 that are not in table2? (2) or to select rows that only exist in one of the tables? (3) or delete rows from table1 that don't exist in table2? (4) or delete rows from both tables that don't exist in the other table? (5) or something else still?
  • What if we had row '4 , 5' in table 2?
  • @Jeevitha, please select and mark correct answer. P.S. You can also give your vote to any answwer that was useful to you...
  • You should never use a group by without aggregate functions
  • Can you please explain why? I am not selecting any field which is not in the group by clause.
  • Please refer to dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
  • @P.Salmon Aggregate function is used - in HAVING clause.
  • @P.Salmon the linked article doesn't say that never use a group by without aggregate functions, it says don't select columns without aggregate functions if they are not included in the group by.