Help with DELETE query

[poppybush]

I am currently writing a piece of software that allows an administrator
to remove 'n' amount of software licenses from a database. Let's say
that they want to delete 6 instances of MS Access and there are 10 in
the database; there should be 4 left over. If I loop through my SQL
string 6 times with the following SQL string it will delete ALL
instances of Microsoft Access in the database:

For i = 1 To intNumToDelete

strSQL = "DELETE * FROM tblSoftware WHERE SoftwareTitle = '" &
strSoftwareTitle & "';"
Dim objCommand As New OleDb.OleDbCommand(strSQL, objConn)
objCommand.ExecuteNonQuery()

Next

I can't seem to figure out a way to only delete 6 of them. If worst
comes to worst I could do the following:
1) subtract intNumToDelete from intMaxToDelete (count of MS Access)
2) DELETE all instances of MS Access and then
3) INSERT the difference from 1

There has got to be a simpler way to achieve this. Any suggestions?

 

[John Vinson]

I am currently writing a piece of software that allows an administrator
to remove 'n' amount of software licenses from a database. Let's say
that they want to delete 6 instances of MS Access and there are 10 in
the database; there should be 4 left over. If I loop through my SQL
string 6 times with the following SQL string it will delete ALL
instances of Microsoft Access in the database:

For i = 1 To intNumToDelete

strSQL = "DELETE * FROM tblSoftware WHERE SoftwareTitle = '" &
strSoftwareTitle & "';"
Dim objCommand As New OleDb.OleDbCommand(strSQL, objConn)
objCommand.ExecuteNonQuery()

Next

Try doing it all in one query:

Dim strSAL As String
strSQL = "DELETE * FROM tblSoftware WHERE PrimaryKey IN" _
& " (SELECT TOP " & intNumToDelete & " PrimaryKey FROM tblSoftware _
& " WHERE SoftwareTitle = '" & strSoftwareTitle & "');"

Assuming you have a Primary Key in tblSoftwareTitle, and (as is
apparent) you don't care which six get deleted, just so six do, this
should do the trick.

If you don't have a primary key there's no good way to delete six of
ten identical records; Access would insist on knowing *which* records
you want to delete, by their contents.

John W. Vinson[MVP]

 

[poppybush]

Thanks John. I will tinker with this for a little bit and get back to
you.

 

[poppybush]

How about awesome?

 

[John Vinson]

How about awesome?

<g> I'll settle for "that works OK"...

John W. Vinson[MVP]

 

[poppybush]

Thanks again for this help John. I was curious, how well would this
work for INSERT query?

 

[John Vinson]

Thanks again for this help John. I was curious, how well would this
work for INSERT query?

I'm not quite clear what you want the insert query to do. Explicate?

John W. Vinson[MVP]

 

[poppybush]

Sorry, I meant UPDATE query. Same scenario as before, except I want to
update where EmployeeName value is Null.

 

[John Vinson]

Sorry, I meant UPDATE query. Same scenario as before, except I want to
update where EmployeeName value is Null.

A TOP VALUES query won't be updateable I fear. I still don't
understand: what records do you want updated, and what do you want
them updated TO?

John W. Vinson[MVP]

 

[poppybush]

The software licenses are listed in the database with ID, EmployeeName,
SoftwareTitle. The EmployeeNames are set to Null because not all of
them are assigned. (Side note: I am in the process of redoing the
database structure but for now I need a quick fix).

Let's say there are 3 available licenses for Visual Studio. The system
administrator only wants to assign 2 of those licenses. Instead of
updating all 3 fields where EmployeeName is null and softwareTitle is
Visual Studio, I would like to only update 2. The two users are drawn
from a listbox that is populated by active directory. The program
calculates how many free licenses are available and will not allow the
administrator to select more users than there are licenses for.

 

[poppybush]

Anyone?

 

[John Vinson]

The software licenses are listed in the database with ID, EmployeeName,
SoftwareTitle. The EmployeeNames are set to Null because not all of
them are assigned. (Side note: I am in the process of redoing the
database structure but for now I need a quick fix).

Let's say there are 3 available licenses for Visual Studio. The system
administrator only wants to assign 2 of those licenses. Instead of
updating all 3 fields where EmployeeName is null and softwareTitle is
Visual Studio, I would like to only update 2. The two users are drawn
from a listbox that is populated by active directory. The program
calculates how many free licenses are available and will not allow the
administrator to select more users than there are licenses for.

Sorry for being late getting back to you poppybush.

Since there is nothing (apparently) within the record to indicate
WHICH two should be updated, this is pretty difficult! A Top Values
query is not updateable, I don't think. What you might try (and again,
this may not work, post back if it doesn't) would be to use a
Subquery:

UPDATE tablename SET SoftwareTitle = "Visual Studio"
WHERE ID IN
(SELECT TOP 2 [ID] FROM tablename AS X
WHERE X.EmployeeName IS NULL
ORDER BY [ID])


John W. Vinson[MVP]

 

[poppybush]

Thanks a lot John. I will get back to you.

- Paul

 

[poppybush]

<napoleon dynamite voice>Sweeeet!</napoleon dynamite voice>

Amazing work John. Thanks a million!