If it was me writing the code, I'd use two different update statements:
a) an UPDATE to initialize the DateTime to Now() and set the counter to 1
when the page is first hit
b) another UPDATE to increment the counter on all of the remaining hits
Something like this (assuming Java is your programming language):
// Logic to display the rest of the web page
...
// Obtain the current row for the counter.
getCurrentCounterRow();
// Store the current counter value in a variable
counter = ; //value obtained from current row
// Adjust the counter row depending on the value of the counter
if (counter == 0) {
update COUNTER_TABLE
set CounterValue = 1;
CounterStartDateTime = now();
}
else {
update COUNTER_TABLE
set CounterValue = CounterValue + 1;
}
// Display the counter value that applies after the IF statement was
executed.
...
etc.
Just my two cents worth....
Rhino
----- Original Message -----
From: "Terry Riley" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 09, 2004 12:11 PM
Subject: Update field conditionally
> Using v4.0.15 on WinNT under Apache.
>
> For my sins, the client has insisted on creating a page counter!
>
> The fields are to be CounterCode (Varchar 10), CounterValue (Int 10) and
> CounterStartDateTime (DateTime).
>
> Setting up the table is no problem. However, client wants to have the
> CounterStartDateTime field updated to Now() only on the first hit to that
> page, so that it can be reported as the start of the count (logically).
> Otherwise, it remains as a NULL value, and the CounterValue remains as 0.
>
> I have tried to find out if it is possible to do a single-pass update,
> changing the CounterValue from 0 to 1 and the CounterStartDateTime to the
> current time on condition that it is currently NULL, with something like:
>
> UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime =
> (IF CounterStartDateTime IS NULL, Now())
>
> without success.
>
> I've looked through the on-line manual, and cannot find any reference to
> such conditional updates. Perhaps I missed it.
>
> Any clues, please?
>
> Cheers
> Terry Riley
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
