I have the table below where I have two columns per client. I want to delete rows in Column B if it is in Column A per Client. In this example we would remove rows with "123" in Column B for Tom since it is in Column A.
Sorry for the confusion.

Output

CodePudding user response：

You can use either not exists clause or not in clause to do that.

select t1.*
from Your_Table t1
where not exists (
  select null 
  from Your_Table t2 
  where t2.Client = t1.Client and t2.Column_A = t1.Column_B
  )
;

or

select t1.*
from Your_Table t1
where (t1.Client, t1.Column_B) not in (
  select t2.Client, t2.Column_A 
  from Your_Table t2
)
;

demo

CodePudding user response：

I hope this will help you. this is the MySql procedure

CREATE PROCEDURE DBname.deleteRecord()
BEGIN

DROP TABLE IF EXISTS `temp`;
create table temp select    b.Column2  as Column2  from  
clienttable  a  inner join 
clienttable  b where  a.Column1  = b.Column2 AND a.client  =   b.client ; 

delete   FROM  clienttable  WHERE  Column2 in (select  b.Column2    
from  temp b );

SELECT * from  clienttable ;


END

Call

call DBname.deleteRecord()

CodePudding user response：

DELETE FROM tableclients AS A 
WHERE A.columnB IN (SELECT B.columnA 
                    FROM tableclients AS B 
                    where B.client = A.client)

This should work out, It's a simple and effective query hope you'll get your required results from it

CodePudding user response：

You can try this:

declare @TableIntersect table(Name nvarchar(max))

insert into @TableIntersect(Name) SELECT Name  
    FROM ColumnA  
    INTERSECT  
    SELECT Name
    FROM ColumnB

delete from YourTable where ColumnA in (select Name from @TableIntersect)
delete from YourTable where ColumnB in (select Name from @TableIntersect)

CodePudding user response：

you can do it simplicity by doing that

delete from table  where column2 in (select column1 from table)

I tested it and got actual result