UPDATE step thru every record w/out unique id field

[Rey]

How can I update every record of a given table one by one.
I need to update a field (date) with a different, random date for each
record.

loop {

generate random date;
update one record with new date;
goto new record

}

Problem. Table does not have a unique id field

table (item, date)

peaches - 2003-05-13
pears - 2003-11-24
peaches - 2004-01-04

the table is a given... meaning I have to work with it the way it is.

Thanks,
Rey

[Aggro]

Rey wrote:

How can I update every record of a given table one by one.
I need to update a field (date) with a different, random date for each
record.

update tablename set date=(*);

Where you need to replace (*) with a sql code that creates a random date
for you. See function called rand() to get started with it. You can
either call rand once to create the date in days and convert the value
into date. Or you can call int 3 times, 1 for year, 1 for month and 1
for day, and create the date from those values. Let us know if this gets
too difficult for you.

For rand():
http://www.mysql.com/doc/en/Mathematical_functions.html

For date and time functions which you need when converting rand() values
into dates:
http://www.mysql.com/doc/en/Date_and...functions.html

[Aggro]

Rey wrote:

How can I update every record of a given table one by one.
I need to update a field (date) with a different, random date for each
record.

update tablename set date=(*);

Where you need to replace (*) with a sql code that creates a random date
for you. See function called rand() to get started with it. You can
either call rand once to create the date in days and convert the value
into date. Or you can call int 3 times, 1 for year, 1 for month and 1
for day, and create the date from those values. Let us know if this gets
too difficult for you.

For rand():
http://www.mysql.com/doc/en/Mathematical_functions.html

For date and time functions which you need when converting rand() values
into dates:
http://www.mysql.com/doc/en/Date_and...functions.html

[Rey]

Will this not set the SAME date for all records?

Rey
"Aggro" <sp**********@yahoo.com> wrote in message
news:vT**************@read3.inet.fi...

Rey wrote:

How can I update every record of a given table one by one.
I need to update a field (date) with a different, random date for each
record.

update tablename set date=(*);

Where you need to replace (*) with a sql code that creates a random date
for you. See function called rand() to get started with it. You can
either call rand once to create the date in days and convert the value
into date. Or you can call int 3 times, 1 for year, 1 for month and 1
for day, and create the date from those values. Let us know if this gets
too difficult for you.

For rand():
http://www.mysql.com/doc/en/Mathematical_functions.html

For date and time functions which you need when converting rand() values
into dates:
http://www.mysql.com/doc/en/Date_and...functions.html

[Rey]

Will this not set the SAME date for all records?

Rey
"Aggro" <sp**********@yahoo.com> wrote in message
news:vT**************@read3.inet.fi...

Rey wrote:

How can I update every record of a given table one by one.
I need to update a field (date) with a different, random date for each
record.

update tablename set date=(*);

Where you need to replace (*) with a sql code that creates a random date
for you. See function called rand() to get started with it. You can
either call rand once to create the date in days and convert the value
into date. Or you can call int 3 times, 1 for year, 1 for month and 1
for day, and create the date from those values. Let us know if this gets
too difficult for you.

For rand():
http://www.mysql.com/doc/en/Mathematical_functions.html

For date and time functions which you need when converting rand() values
into dates:
http://www.mysql.com/doc/en/Date_and...functions.html

[Aggro]

Rey wrote:

Will this not set the SAME date for all records?

Small example:

mysql> select * from randomdate;
+------------+
| randomdate |
+------------+
| 2004-04-04 |
| 2004-04-04 |
| 2004-04-04 |
| 2004-04-04 |
| 2004-04-04 |
| 2004-04-04 |
+------------+

mysql> update randomdate set randomdate=
date_sub(curdate(),interval rand()*365 day);

mysql> select * from randomdate;
+------------+
| randomdate |
+------------+
| 2003-11-05 |
| 2003-09-10 |
| 2003-08-31 |
| 2003-12-24 |
| 2003-08-25 |
| 2004-01-15 |
+------------+

[Aggro]

Rey wrote:

Will this not set the SAME date for all records?

Small example:

mysql> select * from randomdate;
+------------+
| randomdate |
+------------+
| 2004-04-04 |
| 2004-04-04 |
| 2004-04-04 |
| 2004-04-04 |
| 2004-04-04 |
| 2004-04-04 |
+------------+

mysql> update randomdate set randomdate=
date_sub(curdate(),interval rand()*365 day);

mysql> select * from randomdate;
+------------+
| randomdate |
+------------+
| 2003-11-05 |
| 2003-09-10 |
| 2003-08-31 |
| 2003-12-24 |
| 2003-08-25 |
| 2004-01-15 |
+------------+