SQLite Forum

Delete duplicate rows with an extra character
Hello all,

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


        player_tag TEXT,

        update_date TEXT,

        max_trophies TEXT,


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!