ODBC Update statement

[ericm]

I am guessing at this but can someone take a look at this and tell me whats wrong.
What I am trying to do is with ODBC logging to a access database I want to just update the record from a test instead of inserting a new record. Even better would be to update the record and if not exist then insert record but that might be asking to much. Anyways here is what I have tried.
Update HM SET (HM.Da, HM.Alive) = ('%Date%',left('%AliveRatio%',instr('%AliveRatio%',' %')-1) ) WHERE (((HM.TestName) = '%TestName%'))

[Igorek]

MySQL
TABLE `oper`:
`Object` char(128) character,
`Reply` char(16) character,
`Date_Time` datetime,
`Status` char(16) character,

ODBC logging:
INSERT INTO oper (Object, Reply, Date_Time, Status) VALUES ('%TestName%', '%Reply%', NOW(), '%Status%') ON DUPLICATE KEY UPDATE Object='%TestName%', Reply='%Reply%', Date_Time=NOW(), Status='%Status%'

[ericm]

Thank YOu
You saved me alot of work.

[ericm]

I tried and get some error about ; expected at end of statement. I tried to add the semicolon but still get the same error.

[KS-Soft Europe]

What exact error do you see? What database server do you use?

Regards,
Max

[KS-Soft Europe]

BTW. Since version 6.0 undocumented ODBCLog_NoPercentSign option has been implemented. ODBCLog_NoPercentSign option directs HostMonitor to remove trailing percent sign (%) from test related macro variables (such as %Reply%, %AliveRatio%, %DeadRatio%...) if the macro variable is used in SQL query. "Undocumented" means that the option is not accessible thru GUI and you should add a "ODBCLog_NoPercentSign=1" line into [Misc] section of hostmon.ini file. http://www.ks-soft.net/hostmon.eng/news.htm
So, now you do not need to use such complex syntax like left('%AliveRatio%',instr('%AliveRatio%',' %')-1)

Regards,
Max

[Igorek]

I tried and get some error about ; expected at end of statement. I tried to add the semicolon but still get the same error.

Expression "ON DUPLICATE KEY UPDATE" is accessible only in MySQL, since version 4.1.0