mardi 11 août 2015

Removing Duplicate Rows based on

I have such a database structure :

CREATE TABLE `tenant_counter` (
    `id`    TEXT,
    `date`  TEXT,
    `time`  TEXT,
    `date_time` TEXT,
    `sayacdeger`    REAL,
    PRIMARY KEY(id,date_time)
);

In some rows, id, date and sayacdeger occurs multiple times with all keeping their values. So I need to keep only one of them. I tried :

delete from tenant_counter 
where exists (
    select 1 from tenant_counter r
    where r.id = tenant_counter.id
    and r.date = tenant_counter.date
    and r.sayacdeger = tenant_counter.sayacdeger
)

But that deletes all duplicated rows.

Aucun commentaire:

Enregistrer un commentaire