MySLQ, update by date/number

[Sims]

Hi,

I have a table with 3 columns, something like...

NAME, VALUE, DATE, (String(255), int(11), int(11)).

And for argument lets say the data is

row[0] = "A", 10, 1076968526
row[1] = "A", 10, 1076710733
row[2] = "A", 10, 1076709644

I want to update the row where the name = "A" and set the value to 11. But i
only want to update the latest date, (row[0]), in this case where DATE is
the higher.
How would i run a query to update a row where one on the value is the
highest.

Many thanks

Sims

[David Mackenzie]

On Tue, 17 Feb 2004 07:44:10 +0200, "Sims" <si*********@hotmail.com>
wrote:

Hi,

I have a table with 3 columns, something like...

NAME, VALUE, DATE, (String(255), int(11), int(11)).

And for argument lets say the data is

row[0] = "A", 10, 1076968526
row[1] = "A", 10, 1076710733
row[2] = "A", 10, 1076709644

I want to update the row where the name = "A" and set the value to 11. But i
only want to update the latest date, (row[0]), in this case where DATE is
the higher.
How would i run a query to update a row where one on the value is the
highest.

According to:
http://www.mysql.com/doc/en/UPDATE.html

you should be able to write
update table set value=11 where name='A' order by date desc limit 1

but I haven't tried it.

There is a bit which explains differences in the behaviour of LIMIT
between MySQL versions. Check that carefully for your version.

--
David ( @priz.co.uk )

[Tony Marston]

Each of those records should have a primary key, so after you have read the
record(s) in and decided which one you want to update it is a simple matter
of issuing the following query:
UPDATE table SET value='11' where pkey='value'.

If you will always want to update the record with the latest date then you
can ensure that it is returned as the first row with the following query:
SELECT * FROM table ORDER BY date DESC LIMIT 1

--
Tony Marston
http://www.tonymarston.net
"Sims" <si*********@hotmail.com> wrote in message
news:c0*************@ID-162430.news.uni-berlin.de...

Hi,

I have a table with 3 columns, something like...

NAME, VALUE, DATE, (String(255), int(11), int(11)).

And for argument lets say the data is

row[0] = "A", 10, 1076968526
row[1] = "A", 10, 1076710733
row[2] = "A", 10, 1076709644

I want to update the row where the name = "A" and set the value to 11. But i only want to update the latest date, (row[0]), in this case where DATE is
the higher.
How would i run a query to update a row where one on the value is the
highest.

Many thanks

Sims