Set mySQL table field by using NOW()

Hi all,

I am trying to set a field in a mySQL table named LastUpdated equal t
the current date time by using the NOW() function in the SQL area of
recordset. Dreamweaver says I have an error in my SQL syntax, but
can't see where. Here is the SQL code:

SELECT RX, Alternate, TX, Source, A1, A2, A3, A4, Status, Comments
LastUpdated, Initials
FROM maintable
WHERE RX = colname
SET LastUpdated = NOW()

Why does this not work? Is it because NOW() is a mySQL function an
not a SQL fnuction? How can I make this work?

Thanks in advance
-
walfred
-----------------------------------------------------------------------
Posted via http://www.forum4designers.co
-----------------------------------------------------------------------
View this thread: http://www.forum4designers.com/message96417.htm

.oO(walfredo)

SELECT RX, Alternate, TX, Source, A1, A2, A3, A4, Status, Comments,
LastUpdated, Initials
FROM maintable
WHERE RX = colname
SET LastUpdated = NOW()

Why does this not work? [...]

Use SELECT to fetch data from the db, INSERT to add new data and UPDATE
to change existing data. If you want to update a record the query should
look something like

UPDATE maintable SET LastUpdated = NOW() WHERE RX = colname;

Another nice feature of MySQL is the column type TIMESTAMP. Fields of
this type are updated automagically by the db whenever the record is
modified. Might be worth a look in this case.

HTH
Micha

Michael Fesser wrote:

*.oO(walfredo)

Another nice feature of MySQL is the column type TIMESTAMP. Field
of
this type are updated automagically by the db whenever the record is
modified. Might be worth a look in this case.

HTH
Micha *

Ok cool, that updated when I changed the column type to TIMESTAMP.
Thanks! Also, what would I set the default value to be if I wanted i
to format like 0000-00-00 00:00:00:00 As it stands, it only returns
string like 20040715092204. Thanks!
-
walfred
-----------------------------------------------------------------------
Posted via http://www.forum4designers.co
-----------------------------------------------------------------------
View this thread: http://www.forum4designers.com/message96417.htm

.oO(walfredo)

Ok cool, that updated when I changed the column type to TIMESTAMP.
Thanks! Also, what would I set the default value to be if I wanted it
to format like 0000-00-00 00:00:00:00 As it stands, it only returns a
string like 20040715092204.

Use the function DATE_FORMAT() when fetching the date from the db.

MySQL Manual | 13.5 Date and Time Functions
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

HTH
Micha