Skip to content

Instantly share code, notes, and snippets.

@leibovic

leibovic/gist:2305880

Forked from rnewman/gist:2305471
Created April 4, 2012 21:35
Show Gist options
  • Save leibovic/2305880 to your computer and use it in GitHub Desktop.
Save leibovic/2305880 to your computer and use it in GitHub Desktop.
Download ZIP
Start of a query
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment