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]

Re: MySLQ, update by date/number

On Tue, 17 Feb 2004 07:44:10 +0200, "Sims" <siminfrance@ho tmail.com>
wrote:
[color=blue]
>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.[/color]

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]

Re: MySLQ, update by date/number

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" <siminfrance@ho tmail.com> wrote in message
news:c0s9nl$1ab ono$1@ID-162430.news.uni-berlin.de...[color=blue]
> 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[/color]
i[color=blue]
> 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
>
>[/color]