mySQL update question

Question

Hey all i im getting this error when trying to update a table in my database:

Every derived table must have its own alias

Here is my mysql query i am trying to update with:

 UPDATE (SELECT clientinfo.idNumber, clientinfo.theCompName, clientoffers.idNumber, 
 clientoffers.expTimeWaiting, clientoffers.theDateStart,
 clientoffers.theDateEnd, clientoffers.theOffer, clientoffers.isActive, 
 clientoffers.theType, clientoffers.fixedAmount, clientoffers.fixedUsed
 FROM clientinfo, clientoffers
 WHERE clientoffers.idNumber = clientinfo.idNumber
 AND clientoffers.theDateEnd >= '2010-06-03'
 AND clientoffers.theDateStart <= '2010-06-03'
 AND clientoffers.idNumber = 'NB351560'
 AND clientoffers.isActive = 'YES')
 SET clientoffers.fixedUsed = clientoffers.fixedUsed + 1

That seems OK but doesn't seem to work for some reason?

Any help would be great! :o)

UPDATE

 sqltemp = "UPDATE clientinfo, clientoffers SET clientoffers.fixedUsed = clientoffers.fixedUsed + 1 WHERE clientoffers.idNumber = clientinfo.idNumber" & _
             "AND clientoffers.theDateEnd >= '2010-06-03'" & _
             "AND clientoffers.theDateStart <= '2010-06-03'" & _
             "AND clientoffers.idNumber = 'NB351560'" & _
             "AND clientoffers.isActive = 'YES'"
        oRecordset.Open sqltemp, oConnection,3,3

It gives me the error of ODBC driver does not support the requested properties.

I'm using Mysql 5.1 with classic ASP.

David

Answer 1

The "clientoffers." isn't valid outside the parentheses, so you need to create an alias for the select statement (try UPDATE (SELECT...) foo SET foo.fixedUsed = foo.fixedUsed + 1

However, it's simpler to have the update work on a single table at a time:

UPDATE clientoffers
SET fixedUsed = fixedUsed + 1
WHERE
EXISTS (SELECT * FROM clientinfo WHERE
clientoffers.idNumber = clientinfo.idNumber)
AND clientoffers.theDatEnd >= '2010-06-03'
AND clientoffers.theDateStart <= '2010-06-03'
AND clientoffers.idNumber = 'NB351560'
AND clientoffers.isActive = 'YES

Except, depending on the MySQL version, the EXISTS subquery syntax may not be supported. If this is the case for you, you can:

a.) Upgrade MySQL or switch to another DB engine b.) Use your code to run the select in a loop, and then, for each returned row, issue a single update statement, remembering to lock both tables.

Answer 2

Why is there a select there? is that even necessary?

Why not:

 UPDATE clientinfo, clientoffers

 SET clientoffers.fixedUsed = clientoffers.fixedUsed + 1

 WHERE clientoffers.idNumber = clientinfo.idNumber
 AND clientoffers.theDateEnd >= '2010-06-03'
 AND clientoffers.theDateStart <= '2010-06-03'
 AND clientoffers.idNumber = 'NB351560'
 AND clientoffers.isActive = 'YES'

or even further, if there is a foreign-key-constraint on the two tables for idNumber (or you can assume every clientoffer entry always has a corresponding clientinfo entry):

 UPDATE clientoffers

 SET clientoffers.fixedUsed = clientoffers.fixedUsed + 1

 WHERE clientoffers.theDateEnd >= '2010-06-03'
 AND clientoffers.theDateStart <= '2010-06-03'
 AND clientoffers.idNumber = 'NB351560'
 AND clientoffers.isActive = 'YES'