SQLite Forum

Delete duplicate rows with an extra character
Login
Hello all,

I would love your help on this. I have a table where:

CREATE TABLE players(

        player_tag TEXT,

        update_date TEXT,

        max_trophies TEXT,

        UNIQUE(player_tag))


The problem is I have some player_tag's which have an extra character I'd like to delete.

      example: "#Y123456" and "Y123456" are both player_tags.


I would like to remove the # from all player tags (i.e. delete #Y654321 if Y654321 exists), but that of course will error with non-unique rows.

      UPDATE players SET player_tag = replace(player_tag,"#","")

So I'm trying to write a command to delete these duplicates, but I'm not sure how to differentiate where player_tag is coming from in this phase:

      DELETE FROM players WHERE EXISTS (SELECT player_tag FROM players WHERE player_tag = replace(original.player_tag,"#",""))


Appreciate anybody's help!
~Adam