Android Question [SOLVED] sqlite DELETE with COUNT ?

[noeleon]

Is it possible to use delete and count in a single statement?

My goal is to delete rows if artist has only 1 song

Dim ccU As Cursor = SQL.ExecQuery("SELECT * FROM forprintsingers")
Dim art as string
Dim progress as int
For i = 0 To ccU.RowCount-1
    ccU.Position = i
    If (i Mod 100) = 0 Then
        Sleep(0)
        progress = ((i / ccU.RowCount) * 100)
        lblStatus.Text = progress & " %" &CRLF& "Please wait"
    End If
    art = ccU.GetString("name")
    If SQL.ExecQuerySingleResult2("SELECT COUNT(*) FROM forprint WHERE artist = ?", Array As String(art)) = 1 Then
        SQL.ExecNonQuery2("DELETE FROM forprint WHERE artist = ?", Array As Object(art))
    End If
Next

Here's my combined statement, doesn't work:

SQL.ExecNonQuery2("DELETE FROM forprint WHERE ((SELECT COUNT(*) FROM forprint WHERE artist = ?) = 1) AND artist = ?", Array As Object(art, art))

 

[emexes]

My goal is to delete rows if artist has only 1 song

Is forprint a temporary table? I expect it is, but... if not, and just-inc-case:

I have a natural aversion to deleting data that took time and $$$ to collect, and so I wonder if it would be safer to do the "one-hit wonder" culling in the
Select statements that generate lists/reports.

 

[emexes]

Is forprint a temporary table?

If it is a temporary table, then might be simpler to add another field eg "HowManySongs", populate it with the artist song count, and then delete based on that field ie where HowManySongs < 1.

 

[noeleon]

Is forprint a temporary table? I expect it is, but... if not, and just-inc-case:

I have a natural aversion to deleting data that took time and $$$ to collect, and so I wonder if it would be safer to do that the "one-hit wonder" culling in the
Select statements that generate lists/reports.

yes it is temporary.

Funny thing. I created a small project that I was going to upload here using the above code and it seems to be working.

The error must be somewhere else.

Does this look valid to you?

SQL.ExecNonQuery2("DELETE FROM forprint WHERE ((SELECT COUNT(*) FROM forprint WHERE artist = ?) = 1) AND artist = ?", Array As Object(art, art))

 

[drgottjr]

in line with having a "natural aversion to deleting data that took time and $$$ to collect",
having a given record "marked-as-deleted" is a long-standing db practice.

 

[noeleon]

If it is a temporary table, then might be simpler to add another field eg "HowManySongs", populate it with the artist song count, and then delete based on that field ie where HowManySongs < 1.

I believe that's a better approach. Thank you.

 

[aeric]

First you can check/confirm the rows using SELECT statement.

SELECT artist FROM forprint
WHERE artist = ?
GROUP BY artist
HAVING COUNT(*) = 1

Then replace the SELECT with DELETE

DELETE FROM forprint
WHERE artist = ?
GROUP BY artist
HAVING COUNT(*) = 1