Update top or first record only?!? 1

[Davidmc555]

Hi.

I'm hoping to update a table with record of customer licensing details. The table itself holds information on ClientID, Client name, License, License Date, Activation Date etc. Now, the Client can have many license keys without having activated as the license keys allows access to locked modules within the product. Therefore it's reasonable to assume that the same client ID could appear with multiple license keys but no activation dates.

What I'm hoping to do is make a query that will check for a client ID and license key and update the most recent one based on license date. (A client can request to go back to a previous set of modules on purchase if they want)

So far I have (not working of course)...

UPDATE License
SET  License.licenseActivation = Now()
WHERE ((License.licenseClientID= 1234) AND (License.licenseCode = 1234-1234-1234-1234)) 
ORDER BY license.licenseDateTime DESC;

I tried TOP and MAX but update doesn't seem to like them and all other posts refer to select queries.

Is anyone able to offer some advise?

Much appreciated!

 

[Golom]

UPDATE License [COLOR=red]As L[/color]

SET  L.licenseActivation = Now()

WHERE     L.licenseClientID= 1234 
      AND L.licenseCode = 1234-1234-1234-1234  
      [COLOR=red]AND L.licenseDateTime = 
          (Select MAX(licenseDateTime) From License
           Where License.licenseClientID = L.licenseClientID)[/color]

ORDER BY L.licenseDateTime DESC;

 

[Davidmc555]

Cheers Golom.

Needed a bit of tweaking...

UPDATE License As L

SET  L.licenseActivation = Now()

WHERE     L.licenseClientID= 1234
      AND L.licenseCode = '1234-1234-1234-1234'
      AND L.licenseDateTime =
          (Select MAX(licenseDateTime) From License
           Where License.licenseClientID = L.licenseClientID AND
License.licenseCode = L.LicenseCode);

.. or else it would update the most recent clientID entry only where it should be said against ID and license key. Also, the License key is technically a string hence the quotes.

Thank you again, very much appreciated.

 

[Golom]

Sorry 'bout that. I should have read ALL of your SQL instead of just knee-jerk coding.

 

[Davidmc555]

Hey, it was a lot further than I got so I'm not complaining!

Thank again, big help!