Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Removing Matching Rows

I have two tables - One contains the master data and other contains a subset of that data that shouldn't be there. I cannot easily filter out the data from the master table because it's based on a number of criteria that are not captured in the data. I would like to do opposite of Append. I want to perform a join based on matching values in a column and remove all rows in the master table that are in the second table. 

 

so basically if I have table A and Table B, if [columnA.tableA]=[columnA.tableB] then subtract that row. 

 

I did Left Anti merge but that merges the two tables and not subtract it. I don't want to add columns.  

 

How do I do this?

 

Thx.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Ok.. Left -anti worked. I basically merged the two horizontally and then deleted all the extra columns there were created after the merge to get back original columns. Left-anti only gave me the rows that had matching rows based on the column I selected. 

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    HI Anonymous

     

    I think you are on the right track by merging the two tables horizontally.  I think the next step is to filter out any rows from the merged table where its columns in the merged side are NULL (as in they found no match).  This should filter out rows that did match and leave you with the exceptions.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Ok.. Left -anti worked. I basically merged the two horizontally and then deleted all the extra columns there were created after the merge to get back original columns. Left-anti only gave me the rows that had matching rows based on the column I selected.