Using mysql UPDATE query with LIMIT

[Veus]

Hi,

I have to run an update query when a user deletes a record in a subform
of another form.

The query itself is:

UPDATE crmcontactdetails SET primary = 1 WHERE
crmcontactdetails.primaryFirmID= " & Me.primaryFirmID & " AND
crmcontactdetails.type= '" & Me.type & "' LIMIT 1

Now it works without the LIMIT 1 in it, however i need it there as it
will return multiple rows and i only want to update the first one it
finds.

The theory behind this query is that if a user deletes a contact for a
primaryfirm which is the primary contact for that type i need to set a
new primary contact that way every primaryfirm and type combo will have
a primary contact.

Ive tried the same query in mysql directly and it works, is there any
restrictions in access compared to mysql?

The error it gives is:

Syntax error (missing operator) in query expression
crmcontactdetails.primaryFirmID= 10208 AND crmcontactdetails.type= 'In
House' LIMIT 1

 

[Guest]

Limit is not valid word in access SQL, do you not have a unique table key
that could identify more accurately which record to delete and reference that
instead of just the 1st record found?

TonyT

 

[Veus]

Well not really, as i need to check if other contacts exist for that
primaryFirmID and type. If they do set the first one it gets to to 1.

I cant see how i would go about this without using LIMIT.

Unless...

Could I just select all then loop through the first row only and set
that to 1?

 

[Veus]

Well not really, as i need to check if other contacts exist for that
primaryFirmID and type. If they do set the first one it gets to to 1.

I cant see how i would go about this without using LIMIT.

Unless...

Could I just select all then loop through the first row only and set
that to 1?

 

[Guest]

Ok i'm getting confused now, what data is in the field me.type from your
original code? Is it not a unique contact name or reference?

You must have someway of uniquely identifying your records surely?

Being a bit long winded, after executing your sql statement you could do a
dCount of records matching primaryFirmID with primary = 1, if it's <=0 then
do a dMin on primary with matching primaryFirmID, then another sql statement
to set primary to 1.

Sounds like you may not have enough identyfying fields or too much data in
one table, as you should be able to identify unique records more readily than
having to use the above code, if i'm missing the point I apologise, please
post table fields for Firm and Contact Tables and I will see if there is a
better approach.

TonyT..

 

[Dirk Goldgar]

Hi,

I have to run an update query when a user deletes a record in a
subform of another form.

The query itself is:

UPDATE crmcontactdetails SET primary = 1 WHERE
crmcontactdetails.primaryFirmID= " & Me.primaryFirmID & " AND
crmcontactdetails.type= '" & Me.type & "' LIMIT 1

Now it works without the LIMIT 1 in it, however i need it there as it
will return multiple rows and i only want to update the first one it
finds.

The theory behind this query is that if a user deletes a contact for a
primaryfirm which is the primary contact for that type i need to set a
new primary contact that way every primaryfirm and type combo will
have a primary contact.

Ive tried the same query in mysql directly and it works, is there any
restrictions in access compared to mysql?

The error it gives is:

Syntax error (missing operator) in query expression
crmcontactdetails.primaryFirmID= 10208 AND crmcontactdetails.type= 'In
House' LIMIT 1

Are you trying to update a MySQL database via an Access front-end, or
are you trying to update a table stored in a Jet database (.mdb file)?
If you are updating a MySQL back-end, you could use a pass-through query
to send the SQL directly to MySQL for execution.

 

[Veus]

Right ok.

I have a list of contacts (listed in crmcontact details) they all have
a primary key which is an auto incremement number.

I cant use the primary key as I dont know what it would be.
Each contact has a primary key, a type (either In House, Membership,
General) and the relevant details.

I assume im using a pass-through query but it doesnt work:

strSQL = "UPDATE crmcontactdetails SET primary = 1 WHERE
crmcontactdetails.primaryFirmID= 10208 AND crmcontactdetails.type= 'In
House' LIMIT 1" CurrentDb.Execute (strSQL), dbFailOnError

I am trying to update a mysql table.

Thanks for the suggestion of using the MIN and Dcount, ill have a look.

 

[Dirk Goldgar]

Right ok.

I have a list of contacts (listed in crmcontact details) they all have
a primary key which is an auto incremement number.

I cant use the primary key as I dont know what it would be.
Each contact has a primary key, a type (either In House, Membership,
General) and the relevant details.

I assume im using a pass-through query but it doesnt work:

strSQL = "UPDATE crmcontactdetails SET primary = 1 WHERE
crmcontactdetails.primaryFirmID= 10208 AND crmcontactdetails.type= 'In
House' LIMIT 1" CurrentDb.Execute (strSQL), dbFailOnError

I am trying to update a mysql table.

That's not a pass-through query. A pass-through query is one that is
passed directly to the database server to be executed, and not
intepreted by Access or Jet at all. You can create permanent
pass-through queries and save them on the Queries tab of the database
window -- you'll find discussion of that in the help file -- but
creating and executing one on the fly is another matter. Here's one way
to do it. I'm assuming that "crmcontactdetails" is the name of a linked
table in your database, so we can grab its Connect property and use it
for our temporary pass-through query:

'----- start of code -----
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
qdf.Connect = db.TableDefs("crmcontactdetails").Connect

qdf.SQL = _
"UPDATE crmcontactdetails SET primary = 1 " & _
"WHERE primaryFirmID= 10208 " & _
"AND crmcontactdetails.type= 'In House' " & _
"LIMIT 1"

qdf.ReturnsRecords = False

qdf.Execute dbFailOnError

Set qdf = Nothing
Set db = Nothing
'----- end of code -----