468,247 Members | 1,330 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,247 developers. It's quick & easy.

MySLQ, update by date/number

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
Jul 17 '05 #1
2 1937
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 )
Jul 17 '05 #2
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

Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ray | last post: by
5 posts views Thread by cover | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.