Sqlite Delete Duplicates by value

[InunoTaishou]

I have a table with 9 columns and at some point I add an incomplete entry, it might have 2 columns filled in, it might have all of them. When I come back to the table to extract the items I check to see if there are duplicates, pulling out the row with all the information I need but now I need to delete all those duplicates.

Here's a small reproducer for what I'm trying to accomplish. I thought it would be easy with LIMIT but that gives a syntax error.

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>

_SQLite_Startup()
Global $hSqDb = _SQLite_Open("")
Global $iRows = 0
Global $iColumns = 0
Global $aResults = Null
Global $iHighestRating = 0
Global $iLowestRating = 0

_SQLite_Exec($hSqDb, "CREATE TABLE IF NOT EXISTS websites (url TEXT, lastVisit TEXT, nextVisit TEXT, rating INTEGER);")
_SQLite_Exec($hSqDb, "INSERT INTO websites VALUES ('https://www.google.com/', '4/10/2016.0012', '4/10/2016.1212', 8), ('https://www.google.com/', Null, Null, Null), ('https://www.bing.com', '4/09/2016.0936', '4/10/2016.0936', 7), ('https://www.yahoo.com.com', '4/05/2016.2200', '4/15/2016.4000', 4);")

$aResults = _SqLite_GetAllRows($hSqDb, "websites", "url", 'https://www.google.com/')

For $i = 1 to UBound($aResults) - 1
    $iHighestRating = Max($iHighestRating, $aResults[$i][3])
    $iLowestRating = Min($iLowestRating, $aResults[$i][3])
Next

; Limit gives syntax error
_SQLite_Exec($hSqDb, "DELETE FROM websites WHERE url = 'https://www.google.com/' LIMIT " & UBound($aResults) - 2 & ";")

_SQLite_Close($hSqDb)
_SQLite_Shutdown()

Func Min(Const $lhs, Const $rhs)
    Return ($lhs < $rhs ? $lhs : $rhs)
EndFunc

Func Max(Const $lhs, Const $rhs)
    Return ($lhs > $rhs ? $lhs : $rhs)
EndFunc

Func _SqLite_GetAllRows(ByRef $hSqLite, Const $sTable, Const $sWhereKey, $sWhereValue)
    Local $iRows = 0
    Local $iColumns = 0
    Local $aReturn = Null
    Local $iGetAttempts = 100
    If ($sWhereValue <> "" and Not (StringIsInt($sWhereValue) or StringIsFloat($sWhereValue))) Then $sWhereValue = "'" & $sWhereValue & "'"
    Do
        _SQLite_GetTable2d($hSqLite, "SELECT * FROM " & $sTable & " WHERE " & $sWhereKey & " = " & $sWhereValue & ";", $aReturn, $iRows, $iColumns)
    Until (_SQLite_ErrCode() = $SQLITE_OK Or _SQLite_ErrCode() = $SQLITE_NOTFOUND or $iGetAttempts = 0)
    Return ($iRows > 1 ? SetExtended($iRows, $aReturn) : SetError(-1, 0, ""))
EndFunc

To clarify, I need to delete only the rows with the specified value and not remove the other duplicates.

Edited April 10, 2016 by InunoTaishou

[InunoTaishou]

And after half an hour of googling and talking to a friend who works with MySql the solution:

_SQLite_Exec($hSqDb, "DELETE FROM websites WHERE rowid IN (SELECT rowid FROM websites WHERE url = 'https://www.google.com/' ORDER BY rating asc LIMIT 3)")

Remove all the google urls except one and sort it by the rating, making it remove the null items first (or lowest number first, my example just had null)