CODEWITHSUNDEEP
sql
James Gu
James Gu

Reputation: 1392

SQL remove duplicated rows with names alike

My table contains entries with name like this:

12345
A12345
234567
A234567
A99999

I'd like to remove the rows that has Axxxxx where the xxxxx are existing in the table. So in my example I'd like to leave with:

12345
234567
A99999

but remove the A12345 and A234567 since 12345 and 234567 are in the table...

Upvotes: 1

Views: 388

Answers (5)

onedaywhen
onedaywhen

Reputation: 57023

DELETE 
  FROM MyTable
 WHERE name IN (
                SELECT name
                  FROM MyTable AS T1
                INTERSECT
                SELECT 'A' + name
                  FROM MyTable AS T1
               );

Upvotes: 0

Bohemian
Bohemian

Reputation: 425043

This will delete all rows with Axxxxx where there is a row for xxxxx:

delete from mytable
where name in (
    select CONCAT('A', name)
    from mytable
    where name not like 'A%')
)

Edited:

I have the logic reversed in the previous version

Upvotes: 0

user868322
user868322

Reputation: 5

delete 
  from #mytable 
 where exists ( select 'x' 
                  from #mytable b
                 where #mytable.col = 'A' + b.col );

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

This is what I thought of:

delete from Data2 where id in (
    select d.id from Data d
    left join Data1 d1 on (d.id = CONCAT('A', d1.id))
    where d1.id is not null
)

You can play with it here

This is a query with a substring... but I think it is a little less efficient (you can tell us later):

delete from Data where id in (
    select d2.id from Data1 d1
    join Data2 d2 on (d1.id = substring(d2.id, 2))
);

Note mysql substring considers the first character to be numbered as 1 not 0.

You can play with it here

Upvotes: 0

AJP
AJP

Reputation: 2125

delete from mytable 
where name in (select b.name
  from mytable a
  INNER JOIN mytable b
    on a.name = SubString(b.name, 1, LEN(b.name))
  WHERE Substring(a.name, 0, 1) <> 'A' AND Substring(b.name, 0, 1) = 'A'
    )

try this. u might have to twick substring. but you get the idea.

Upvotes: 0

Related Questions