Start of a query

gistfile1.txt

Due to limitations of SQLite, we need to make a temp table for this:

CREATE TEMP TABLE duped_urls AS
SELECT url, SUM(visits) AS total, MAX(modified) AS latest, MAX(_id) AS winner
FROM history
GROUP BY url
HAVING count(url) > 1;

UPDATE history
SET visits = (SELECT total FROM duped_urls WHERE duped_urls.url=history.url),
    modified = (SELECT latest FROM duped_urls WHERE duped_urls.url=history.url),
    deleted = (_id <> (SELECT winner FROM duped_urls WHERE duped_urls.url=history.url))
WHERE url IN (SELECT url FROM duped_urls);

DROP TABLE duped_urls;