Delete repeated elements from a table

Question

Delete repeated elements from a table

Navigation

#1 by (0 votes)

0

The method takes some elements of a table and places them in another table, what I want is that if the same elements are added again they are eliminated or they can not be added if they already exist.

public void añadirErrores(int[] pregMalas) {
    int preg;
    SQLiteDatabase db = getReadableDatabase();
    SQLiteDatabase dbw = getWritableDatabase();
    for (int i = 0; i < pregMalas.length; i++) {
        preg = pregMalas[i];
        Cursor c = db.rawQuery("SELECT * FROM preguntas where id = " + preg, null);
        if (c.moveToFirst()) {
            do {

                ContentValues values = new ContentValues();
                values.put("idpreg", c.getString(0));
                values.put("pregunta", c.getString(2));
                values.put("respuesta_correcta", c.getString(3));
                values.put("respuesta_falsa_1", c.getString(4));
                values.put("respuesta_falsa_2", c.getString(5));
                values.put("recurso", c.getString(6));
                dbw.insert("testErrores", null, values);
            } while (c.moveToNext());
        }
        c.close();
    }
    dbw.close();
    db.close();
}

sqlite

asked by FranEET 22.01.2017 в 14:25

source

1 answer

convert form ipn paypal php to laravel Form html does not receive variable php from link

score 0 · Answer 1

Hello, you have 2 ways to do it

1: It is with Java doing a Select and those data store them in a list then go through the list and eliminate the duplicates with java 8 It would be like this:

    ArrayList<TuObjeto> listaSql = new ArrayList<>();
    //aqui debes llenar tu lista
    //luego crear un stream en base a tu lista
    Stream<TuObjeto> stream = listaSql.stream().distinct();
    //luego puedes recorrer tu stream y obtener los datos

2 SQLite:

    BEGIN;

    CREATE TABLE test1(first INTEGER, second TEXT, third TEXT)
    --WITHOUT ROWID --SQLite 3.8.2
    ;

    INSERT INTO test1 VALUES (0, 'zero' , 'OK' );
    INSERT INTO test1 VALUES (0, 'zero' , 'OK' );
    INSERT INTO test1 VALUES (0, 'zero' , 'OK' );
    INSERT INTO test1 VALUES (1, 'one'  , 'OK' );
    INSERT INTO test1 VALUES (2, 'two'  , 'OK' );
    INSERT INTO test1 VALUES (3, 'three', 'OK' );

    --Para ver las columnas ocultas.
    SELECT *
    , oid --SQLite
    FROM test1
    ;

    --Para ver sólo los registros duplicados.
    SELECT COUNT(*), first, second, third FROM test1 GROUP BY first, second,third HAVING (COUNT(*) > 1);

    --SQLite.
    --Para borrar todos los repetidos menos uno usando OID.
    DELETE FROM test1
    WHERE oid NOT IN (SELECT min(oid) FROM test1 GROUP BY first, second, third)
    ;

    --Para ver lo que queda finalmente en la tabla.
    SELECT * FROM test1;

    DROP TABLE test1;

    ROLLBACK;

I hope it serves you Regards.