Mysql query for updating next row

I need to display a news-tip daily and my table structure as below.

| id  |  ndate      |News Tip |
+------+----------------------+
| 1541 | NULL       | news1   |
| 1545 | 14-10-2010 | news2   | 
| 1567 | NULL       | news3   |
| 1568 | NULL       | news4   |
| 1582 | NULL       | news5   |
     ....     ....      
| 1702 | NULL       | news217 |

and the query select * from newstip where ndate!=NULL and checks whether ndate matches current date its ok. if not i need to update next row (1567 in this case) to current date and all others to NULL.

id is unique but not in sequence so below query not possible.

update newstip set ndate='15-10-2010' where id=(id+1); //failure

How to solve this 开发者_JAVA技巧situation ?. i'm using php to this process;

Given that the id of the last complete row is stored in :old_id:

UPDATE `newstip` SET `ndate`=15-10-2010` WHERE `id` > :old_id ORDER BY `id` LIMIT 1;

If your id is going to be bigger, you could just query for the next row and update it. In your case:

UPDATE newstip SET ndate = '15-10-2010' WHERE id > 1545 ORDER BY id ASC LIMIT 1

I should mention that this statement is not replication safe.

UPDATE newstip
SET ndate = '15-10-2010'
WHERE id = ( SELECT id FROM newstip WHERE id > 1545 ORDER BY id LIMIT 1 )

This won't update anything if no record has an id > 1545.

Here is a hole procedure in mysql to update all the rows below

CREATE PROCEDURE upDates()

BEGIN

DECLARE dat=date('16-10-2010');

DECLARE idd INT;

DECLARE cur1 CURSOR FOR SELECT id fron newstrip where id>1545 order by id;

read_loop: LOOP

FETCH cur1 INTO a;

IF done THEN

  LEAVE read_loop;

END IF;

update newstrip set ndate=dat where id=a;

dat=DATE_ADD(dat,INTERVAL 1 DAY);

END LOOP;