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

Here is how to get the rowid for delete candidates: SELECT x.rowid FROM players x, players y WHERE substr(x.player_tag,2)=y.player_tag AND substr(x.player_tag,1,1) IN ('#'); . Calling that query "Extras", the delete is: DELETE FROM players WHERE rowid IN (Extras); . It helps here that each row has a unique rowid.

One presumes you mean something like this:

DELETE FROM players o
      WHERE player_tag like '#%'
        AND EXISTS (
                    SELECT 1
                      FROM players
                     WHERE player_tag == substr(o.player_tag, 2)
                    )
;

The original, although it can be made to work, is flawed in that it processes all rows in the table, rather than just the rows that might need to be deleted. If you limit the examiation to only those rows implies that there is always a '#' as the first character so there is no need to search for it.

However, the following statements might be faster:

UPDATE OR IGNORE players
             SET player_tag = substr(player_tag, 2)
           WHERE player_tag like '#%'
;
DELETE FROM players
      WHERE player_tag like '#%'
;

Note that you will need to do pragma case_sensitive_like=1; to use the unique index on player_tag (unless, of course, player_tag is case insensitive) ...

There are two statements.

The first is an UPDATE statement which, for all rows where the player_tag startswith '#', removes the '#' character prefix. For any row in which this change causes a conflict (duplicate player_tag after the update) the update is ignored. This means that the only rows remaining where player_tag commences with '#' are duplicates to be deleted.

The second statement deletes all those duplicates.